[BUG] Incorrect Timestamp Displayed in Tableau Desktop & Tableau Prep from OpenSearch Database

[thomasLoveJesus]

What is the bug?
Incorrect Timestamp Displayed in Tableau Desktop & Tableau Prep from OpenSearch Database.
When we load data into Tableau Desktop or Tableau Prep, the timestamp field incorrectly display the year as 1970. However, the actual timestamp in the OpenSearch JSON is correctly recorded as 2025.

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Please refer to the attached figures.
2. Install the required .taco and drivers for opensearch and Tableau Desktop to communicate.
3. Open Tableau Desktop and connect OpenSearch by OpenSearch Project
4. After reading the data, preview one of the tables containing timestamp
5. One can see the wrong year 1970, should be 2025

What is the expected behavior?
The timestamp should match the data in OpenSearch (2025) and not default to 1970 in Tableau Desktop and Tableau Prep.

What is your host/environment?

• OS: Linux CentOS 7.9 for OpenSearch
• OpenSearch by OpenSearch Project version: 1.3.19
• Connectors for opensearch and Tableau Desktop:
• opensearch_sql_jdbc.taco
• opensearch_sql_jdbc-v1.0.1.taco
• opensearch_sql_jdbc-1.1.0.1.jar
• Tableau Desktop Professional Edition 2024.3.2 (20243.24.1211.0901) Windows version
• Tableau Prep Builder 2024.3 (24.32.24.1209.1136) Windows version

Do you have any screenshots?
Install the required .taco and drivers for opensearch and Tableau Desktop to communicate:

Required .taco files

Required .jar file

OpenSearch Dashboard showing the original timestamp (which is the correct year 2025):

Connect OpenSearch to Tableau:

timestamp in Tableau Desktop showing the wrong year 1970, should be 2025:

Do you have any additional context?
Add any other context about the problem.

[Swiddis]

The problem seems to be a unit mismatch somewhere between OpenSearch and Tableau (either OS -> JDBC or JDBC -> Tableau). In particular, 1736477400 is the timestamp for 2025/01/10 @ 2:50:00 AM as epoch seconds. However, if you read those seconds as milliseconds, you get 1970/01/21 @ 2:21:17 AM (i.e. the same as the epoch 1736477.4 seconds).

Looking over the code to check whether it's the first or the second part, we can see that the culprit is TimestampType.fromValue indirectly passing to the java.sql.Timestamp constructor, which expects milliseconds. To fix the bug, we should add logic to check the magnitude of the timestamps and convert from seconds to milliseconds if necessary.

As a workaround, ensure timestamps are stored as milliseconds instead of seconds.

Marking as good first issue for external contributors.

[thomasLoveJesus]

Hi Swiddis,

Thank you for the workaround suggestion. We created another field in OpenSearch called timestamp_second and let Tableau consume it instead of the default timestamp field. I have found out that when Tableau reads a timestamp, it will cast it into a Date and time data type. However, if we feed Tableau timestamp_second, since Tableau treats it as a Number, I can use the formula provided by Tableau, DATEADD('second', [timestamp_second], #1970-01-01#), to display the intended Date and Time.