Search code examples
sqlitedatetimestampdbeaver

Sqllite query convert timestamp to date

I'm working with DBeaver on Ubuntu Linux, and querying a sqlite database via a org.sqlite.JDBC driver. In a table, a column called "event_date" is apparently of type

ABCevent_date(TIMESTAMP(10))

so when I query it I get a column of big long numbers like :

|event_date   |
|-------------|
|1430434800000|
|1430434800000|
|1430434800000|
|1430434800000|
|1430434800000|
|1433286000000|
|1433286000000|

I've tried lots of things using DATE, and DATETIME and STRFTIME but cannot get these to appear in the query results as a normal date like "2014-05-10", usually just get NULL. How can I convert these numbers to dates? Thx. J

Solution

  • Your dates are unix epoch times with milliseconds.
    You must divide event_date by 1000 to strip off the milliseconds and then use functions date() or datetime():

    SELECT date(event_date / 1000, 'unixepoch')
FROM ...