NaN - date fields are not displayed correctly in superset

[DLoc-poste]

Bug description

Hello, From postgressql database version : 9.6.16 :
Some date fields are displayed correctly in SQL request but are not displayed correctly in superset

I found the issue below but it is closed :

How to reproduce the bug

• In postgresql database version : 9.6.16 , in a date field , insert a value before may before 1936 : 10/01/1901
• display it in a superset 4.0.2 dashboard.

Screenshots/recordings

From database request :

From superset dashboard :

From superset some well displayed :

database field definition :

Superset version

4.0.2

Python version

I don't know

Node version

I don't know

Browser

Firefox

Additional context

No response

Checklist

• [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
• [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
• [X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot[bot]]

Hey @DLoc-poste! I'm Dosu, a bot here to help you with bug fixes and answer your questions. Let's work together to tackle this issue!

The issue you're encountering with date fields displaying as "NaN" in Apache Superset 4.0.2 dashboards is likely due to inconsistencies in how timestamps without explicit timezones are handled. Apache Superset relies on Pandas to read SQL into a DataFrame, and Pandas may ignore the DB-API type code, leading to issues with timestamp serialization. This can result in timestamps being incorrectly coerced, causing them to display as "NaN" in the client.

To resolve this, ensure that both the PostgreSQL database and the Apache Superset backend are configured to use the same timezone, preferably UTC, to avoid incorrect coercion of temporal fields ^[1].

Additionally, I found a similar closed issue related to "NaN" in legend dimensions, which might provide some context: NaN in legend dimension ^[2].

The pull request you mentioned (#24696) does not have specific details available in the repository, so it might not directly address your issue ^[1].

To continue talking to Dosu, mention @dosu.

[sfirke]

I'm using a newer version of Postgres (14 or 15) and can reproduce this in Superset 4.0.2. In SQL Lab I run:

select (GETDATE() - 40000 ) as dt1,
(GETDATE() - 40000 ) as dt2

Then save as a dataset.

Result:

Here is the original report for this bug: https://github.com/apache/superset/issues/24561 I think I failed to reproduce it in that thread because subtracting 31150 days from "now" was no longer far enough back to trigger this behavior. Now at 40000 it should remain a valid bug report for another decade or so at least.

[rusackas]

🎶BATMAAAAAAAN🎶

[dex-k]

I've done some testing, and dates begin to display incorrectly from 1938-04-24, 22:13:21 UTC. This corresponds to a Unix timestamp of -999999999, clearly not a coincidence. While I don't have the time to dig deeper, hopefully this can be of use to someone else.