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 : Apache Superset NaN - date fields are not displayed correctly in superset

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 databse

From superset dashboard : from superset

From superset some well displayed : from superset some ok

database field definition : 2024-09-23_15h30_33

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.

Comment From: 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.

Comment From: 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: Apache Superset NaN - date fields are not displayed correctly in superset

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.

Comment From: rusackas

Apache Superset NaN - date fields are not displayed correctly in superset 🎶BATMAAAAAAAN🎶

Comment From: 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.

Comment From: rusackas

Is anyone still facing this issue? If not, it's at risk of being closed as stale/not-planned, since it's been silent for > 6 months.

Comment From: rusackas

Closing as not-planned, but happy to revisit/reopen this if anyone is still facing it or wants to open a PR.