CODEWITHSUNDEEP
sqlitetimestamp
davidmerrick
davidmerrick

Reputation: 1037

What date format is "623548800"?

I exported the SQLite db from an iOS app and was wanting to run a query based on the date, but I found that it's in a format I don't recognize. As stated above, the latest value is "623548800". I'm assuming this corresponds to today, since I created a record in the app today. This is 9 digits, so it's too short to be a Unix timestamp, which is 10 digits.

The earliest record in the db is "603244800", which likely corresponds to when I started using the app on 2/13/2020. That's a difference of 20,304,000, so it looks like it's using seconds, as it's been 20,312,837 seconds since then.

Is this essentially tracking seconds based on some proprietary date, or is this a known format?

Upvotes: 0

Views: 278

Answers (2)

forpas
forpas

Reputation: 164069

Your dates are Unix Timestamps.
By using any on line converter (like https://www.epochconverter.com) you can find the dates they correspond to.

The latest value 623548800 corresponds to Thursday, October 5, 1989 12:00:00 AM GMT
and the earliest value 603244800 corresponds to Sunday, February 12, 1989 12:00:00 AM GMT.

So it seems like your dates or off by 31 years.
I found a similar case here: Behind The Scenes: Core Data dates stored with 31 year offset?

If you want you can convert them to the format 'YYYY-MM-DD hh:mm:ss' like this:

UPDATE tablename
SET datecolumn = datetime(datecolumn, 'unixepoch', '+31 year')

or:

UPDATE tablename
SET datecolumn = date(datecolumn, 'unixepoch', '+31 year')

if you are not interested in the time part.

Upvotes: 0

Devin Sewell
Devin Sewell

Reputation: 11

623548800 - 603244800 = 20304000

20304000/86400 seconds in 24 hours = 235 days

October 5, 2020 - February 13, 2020 = 235 days

UTC Unix timestamp February 13, 2020 = 1581552000

Like the prior comment said it looks like an offset, it might be a timestamp somewhere in source or in db

Upvotes: 1

Related Questions