Using 0.33 - Looking for some advice on how to best handle date and timestamps in SQLLITE. The latter doesnt have a true date/timestamp datatype (https://github.com/metabase/metabase/issues/3190). If I set a field to DATETIME metabase does attempt to render as a date but filters/grouping fails. Using TIMESTAMP, metabase renders an bigint.
Any thoughts on how best to get this working in SQLLITE short of using a separate date dimension table?
thxs
Hi @brianwilson
What is failing when you use DATETIME? I can filter/group, when testing with Chinook sample.
What is the Field Type of the column? Admin > Data Model > (database) > (table) > (column)
Hi @flamber,
With SQLLITE datetimes can be persisted in a number of forms, if I use EPOCH milliseconds metabase just see the value as a big integer. I tried flipping the data type to create timestamp. This gets the value to render in the table but filter (e.g. Previous 30 years) returns 0 rows, as does group by (month, day or....). No error message.
The epoch millisecond rendered using "create timestamp or create date"
Example Epoch Millisecond values stored in sqllite DATETIME column:
1481039520000
1565637556000
Storing as TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")
e.g. 2019-08-29 16:10:04 seems to work.
I have restrictions on reformating the datetime before insert that prevents me flipping the format.
@brianwilson
Do you see any errors in the Metabase log?
I don’t know what the “perfect” solution would be, but a work-around might be to create Views, where you convert the columns into formats that Metabase plays nice with.
SQLite isn’t a database I work with normally, so I lack knowledge about it’s quirks.
Hi Brian, I am a novice SQLite programmer. I had same problem with timestamps on SQLite with Metabase operating
i was able to solve this, take note of the code above. the order_date field is of the “YYYY-MM-DD HH:MM:SS.SSS” format in my database. by including that "localtime" modifier in the SQLite query, the time offset is corrected. this effectively time synchronizes Java/Metabase timestamp with the text based timestamp in SQLite database records.
hope this helps,
Chuck
Thank you @flamber and @crwheelr. I bit the bullet and revisted the the ETL populating the SQLLite and cast the date to the ISO string format before inserting into the SQLLite field of DATATIME - yes seems weird to “insert” a string into DATETIME field but given SQLLITE support for dates…
This seems to have worked, atleast wrt metabase
Still struggling with using SQLITE dates and timestamps with metabase, including latest release 37.3. While many of the approaches described elsewhere in this thread can allow date times to be correctly displayed, formatted and sorted, the application of filters such as “current hour” fail as metabase appears to query based on the GMT sqlite default - not respecting local time zone or offsets stored with the datetime: ‘2020-12-05 10:55:00 +05:00’. I found some threads describing timezone issues with metabase but still struggling on how to “tell” metabase what time zone to apply to a sqlite data source. Any pointers appreciated.
@brianwilson It would be very helpful if you can provide a sample dataset with steps-to-reproduce.
Sounds like it’s very related to these issues:
https://github.com/metabase/metabase/issues/11703
https://github.com/metabase/metabase/issues/12388
And this PR will likely help a lot on SQLite, because of it’s bad handling of timestamps in general:
https://github.com/metabase/metabase/pull/13860