QRDS can't parse certain date formats that Redash understands

[susodapop]

Issue Summary

One of our customers found a date format that React understands but SQLite does not. This mismatch makes SQLite's date functions fail inexplicably. The only workaround is to perform shot-in-the-dark string manipulation on a field you can't see.

Steps to Reproduce

Execute this SQLite against QRDS

WITH verbose_date AS (
SELECT '2019-12-01T00:02:00.000+0000' as dt
)

SELECT
    '**' || dt || '**' "Original Timestamp (wrapped to prevent Redash parsing)",
    dt "Original timestamp (Redash Parses)",
    date(dt) "SQLite Can't Parse",
    strftime('%Y', dt) "SQLite can't format original timestamp",
    strftime('%Y', '2019-12-01 00:02') "SQLite can format a proper date"
FROM verbose_date

Output

Comments

SQLite can't parse the date when it includes all that timezone information. The disconnect between what React displays and what SQLite understands puzzles the user. When strftime returns null it means you didn't pass it a date. But the app makes it seem that a proper date was returned by the database.

Casting dt as a string makes no difference either since the front-end still guesses the data-type. And forcing the column-type to Text from the Visualization Editor displays a Unix timestamp (1575158520000) instead of the raw result of the query.

The only way to see the raw data from the DB is to add characters that break the front-end parse behavior. Only developers will guess this troubleshooting step.

Technical details:

• Redash Version: v9 Alpha
• Browser/OS: FF / Chrome / Safari
• How did you install Redash: SaaS

[susodapop]

Hat tip to @arikfr for reproducing this.