Older Dates with Year 1937 or less in charts is showing as NAN.

[dishant1691]

Hello, I am using superset version 2.1.0

For older dates around year 1937 and back I am not able to see correct dates in charts, it is showing as NAN.
Although in dataset it is being shown correctly.

Database I have used Databricks SQL, SQL server.

For example I have created a simple table chart by using this dummy date in dataset:

select (current_date() - 31150 ) as dt1, the chart shows NAN value

But if I run this select (current_date() - 31105) as dt1, it shows correct date

So dates value 31150 days back from current date is not being shown correctly. But 31105 days back is correctly shown.
Can you please let me know if there is such limitation in charts and if it is so how can we rectify as we have dates older than 1930 year in our tables

[sfirke]

What do you get when you run the same SQL query directly against the database, without Superset?

[dishant1691]

I am getting exact date

but when trying to show them in Charts it shows NAN

[dishant1691]

@sfirke Can you please check if this issue is version specific in charts.

[sfirke]

I can replicate this bug with SQL Server and Superset 2.1.0.

1. Run this query in SQL Lab and save as a data set:

select (GETDATE() - 31150 ) as dt1,
(GETDATE() - 31100 ) as dt2

2. In the resulting chart creation view, the first date appears as NaN:

I don't know what's happening here.

[dishant1691]

@sfirke
Do you know someone from Superset who can help me on this as this seems to be an issue with Superset charts.

[sfirke]

I agree it seems like a bug in Superset. There's not really anyone "from Superset" because it's an open-source project without a single owner. Anyone could submit a pull request to fix this. I don't think it will be a priority for most developers because it doesn't affect anyone who isn't using old year values.

If you're able to poke around in the Superset code to figure this out, that would be ideal. Otherwise one thing you might be able to do is try testing this on different versions of Superset. If it's fixed in the new 3.0.0rc1, great. If nothing else, try other date values and see if you can learn anything. Does 1838 work? Is there a max date that returns this same NaN?

I thought it could be related to Pandas dates out of bounds, but 1938 seems too modern for that: https://pandas.pydata.org/docs/user_guide/timeseries.html#timestamp-limitations

[sfirke]

I just tested this on 3.0.0rc2 and it appears fixed there. Following my steps above I get:

Maybe it was fixed by #23339 ? h/t to @justinpark for making the connection. At any rate, I'm going to close this issue. Please feel free to re-open or reply if this occurs again in 3.0.0 or latest.