TIMESTAMPs with SQLite


#1

Hello,

I have a SQLite table with a column named Time.

Time is a TIMESTAMP column with the following format:

2005-01-31 00:00:00

After importing the database into metabase, I get

8:00 PM

Is this a bug or am I doing something wrong?
I can see the correct “Time” values in SQLiteDatabaseBrowser.


#2

If you look in :gear: Admin > Metadata Model is the data type correctly set to timestamp?


#3

Yes, it is set to timestamp.


#4

Yep, I can reproduce the problem - and think I localized the area where the bug is.

The current .../driver/sqlite.clj uses regex pattern matching, so it matches type strings beginning with some patterns. There is - probably just by oversight - no definition for “TIMESTAMP” - so it will drop through the list until it hits … yeah, you guessed right: “TIME”…

I’ll re-categorize this topic as #bug-reports

@zooond thanks for surfacing this. I think, until someone can fix this, your options are either to move to another database driver, drop to native SQL and select datetime(time, 'unixepoch') from your_timestamp_table or convert it to a DateTime which should work.


#5

OK, thank you.
I’m glad I could contribute somehow with Metabase.