CODEWITHSUNDEEP
iossqliteunixnsdate
Alessandro Mattiuzzi
Alessandro Mattiuzzi

Reputation: 2467

SQLITE strange timestamp and IOS

I'm trying to display a simple tableview in IOS with data from Sqlite. My database date is stored as a timestamp. I thought was an unix timestamps but if i try to use dateWithTimeIntervalSince1970 i've really strange result.

Examples of date rows stored: 

1352208510267
1352208512266
1352208514266
1352208516266
1352208530266
1352208532265

Upvotes: 2

Views: 2552

Answers (3)

Zak Jacobson
Zak Jacobson

Reputation: 23

I found the answer here. I compared the results with the previous answers:

SELECT strftime('%Y-%m-%d %H:%M:%S', datetime(ZDATE+978307200, 'unixepoch', 'localtime')), datetime(ZDATE, 'unixepoch', 'localtime') FROM ZTABLE

The query with the adjustment for Apple's epoch (Jan 1 2001) gives me the correct date:

"2015-09-29 20:50:51", "1984-09-28 20:50:51"
"2015-09-29 21:03:10", "1984-09-28 21:03:10"
"2015-09-29 21:25:30", "1984-09-28 21:25:30"

Upvotes: 2

CL.
CL.

Reputation: 180192

Unix timestamps are defined as the number of seconds since Jan 1 1970. Just now, this would be about 1365525702.

Your values are one thousand times larger, i.e., they are measured in milliseconds.

Decide whether you actually need the millisecond precision, and then add * 1000 or / 1000 at the appropriate places.

Upvotes: 0

Aran Mulholland
Aran Mulholland

Reputation: 23945

Use a query like this

SELECT datetime(timestamp, 'unixepoch') from YOURTABLENAME
WHERE id = someId;

This should convert it to some readable value.

Have a look here

Upvotes: 2

Related Questions