0

I've received a SQLite file with .db extension. Opening it, I have a column with serial numbers: i.e. 1600414704594 (that should correspond to 2020-10-09 and whatsover time) The db comes from outside and I don't know how that date field has been built. Could you suggest me a query to get a valid date/time from that db column ? I've tried reading this post but none of the given solution returned me a valid (and actual) date, please help me.

ocelot73
  • 41
  • 5
  • If you chop off the 594 on the end, it's the unix timestamp for 2020-09-18 07:38:24... – Shawn Oct 14 '20 at 06:10
  • Does this answer your question? [How to convert Unix epoch time in SQLite](https://stackoverflow.com/questions/14629347/how-to-convert-unix-epoch-time-in-sqlite) – forpas Oct 15 '20 at 16:30

1 Answers1

1

It looks like an Unix time in milliseconds. SQLite's unixepoch modifier expects it in seconds. The conversion is fairly easy :

SELECT DATETIME(1600414704594 / 1000, 'unixepoch')

2020-09-18 07:38:24
bwt
  • 17,292
  • 1
  • 42
  • 60