TIMESTAMPs with SQLite

Bug reports
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.

1 Like
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.

1 Like
6

I have just experienced the same issue running Metabase… v0.34.3, and a SQLite file exported via Python's sqlite3.

  • in Custom Question:
    Despite being defined as either “Creation Date”, “Creation Timestamp”, the date appears as “8:00PM”-like.
  • in SQL query:
    The datetime(time, 'unixepoch') workaround doesn't work, but simply selecting the column in a SQL query actually returns a timestamp.
    Yet I believe the bug has been solved in the meantime, right?

System:

{
  "browser-info": {
    "language": "en-GB",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Linux x86_64; rv:74.0) Gecko/20100101 Firefox/74.0",
    "vendor": ""
  },
  "system-info": {
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "1.8.0_242-b08",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "1.8.0_242",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "25.242-b08",
    "os.name": "Linux",
    "os.version": "5.5.7-200.fc31.x86_64",
    "user.language": "en",
    "user.timezone": "Europe/London"
  },
  "metabase-info": {
    "databases": [
      "sqlite"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "tag": "v0.34.3",
      "date": "2020-02-25",
      "branch": "release-0.34.x",
      "hash": "1a83edb"
    },
    "settings": {
      "report-timezone": "Europe/London"
    }
  }
}
7

I’ve reported the issue mentioned above on Github.

8

Might be of interest: Handling date & timestamps in SQLLITE

storing dates as TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”) seems to have addressed a similar issue to what you describe.

1 Like