superset sqllab and charts complain about pandas timestamp conversion when the dates exceed pandas timestamp bounds

[yeachan153]

Similar to 13661, 18596, 16487 timestamps older than 1677-09-22 00:12:43.145225 and newer than 2262-04-11 23:47:16.854775807 cannot be converted into pandas timestamp.

By the same logic, charts which create a DTTM_ALIAS column in the backend query also cannot contain dates older than the range mentioned above, and trigger the same failure. They are coerced as NaNs, so that the data for dates within the range pandas supports can at least be shown without triggering the same error.

How to reproduce the bug

Sqllab

1. Go to 'Sql Lab'
2. Click on 'Sql Editor'
3. Try select TIMESTAMP '2263-02-02 00:00:00' (this won't work)
4. Try select TIMESTAMP '2262-02-02 00:00:00' (this will work since it's inside the range pandas can support)

Charts

1. Go to 'Charts'
2. Select time series chart
3. In the time column, add the date column which has dates outside the ranges mentioned above
4. Fill in whatever metrics you want to visualise

Expected results

Returns the result correctly.

Actual results

Sqllab:

Casting from timestamp[us] to timestamp[ns] would result in out of bounds timestamp: 9248947200000000

Charts:

Out of bounds nanosecond timestamp: 9999-01-01 00:00:00

Environment

• browser type and version: chrome
• superset version: latest & 1.4.1
• python version: 3.8.12
• postgres version: 13.4

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

There are some similar issues, but reviewer mentioned to open a new issue as the PR for the issue was already closed: #14006

[villebro]

Out of curiosity, is this a theoretical problem or do you have a real need for timestamps that far in the future/history?

[yeachan153]

Hi @villebro, for us we have databases upstream who mark some timestamps as 9999-12-31T00:00:00 to indicate that this data point has not expired/is current.. Not ideal but it's not possible for us downstream to change that

[villebro]

Actually, after I wrote that, it hit me that I used to use 9999-12-31 to indicate active rows on an SCD on an Oracle database back in the day. So I agree, this is a real issue 🙂

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[cjaiello]

Is there still no fix for this?

[yeachan153]

Is there still no fix for this?

Can you try with version 1.5.1 or higher?