Sqlite timestamp to javascript Date object

Question

I'm trying to import an old sqlite database to mongodb using node, this db has several date fields and I can't get the correct data, for example:

370735200 -> Should be 2012/10/01 00:00:00 but it shows:

Wed Sep 30 1981 23:00:00 GMT+0100 (CEST)

I'm doing this:

   var originalDate = Math.round (new Date(data.origDate * 1000))

Another example 360540000 -> Should be 2012/06/05 00:00:00 but it shows:

Fri Jun 05 1981 00:00:00 GMT+0200 (CEST)

The date fields in SQLite db have the type: timestamp

Maybe somebody could advise or suggest some node module to deal with this.

Edited, adding info:

I was told that the sqlite value comes from NSDate, I saw the docs:The sole primitive method of NSDate, "timeIntervalSinceReferenceDate, provides the basis for all the other methods in the NSDate interface. This method returns a time value relative to an absolute reference date—the first instant of 1 January 2001, GMT." But I still wonder how can I translate that to get the correct date.

the dates you show do match those timestamps. the timestamp for 2012-10-01 is 1349049600 and the timestamp for 2012-06-05 is 1338854400 — chiliNUT
– chiliNUT, Commented May 25, 2014 at 18:13

mthierer · Accepted Answer · 2014-05-27 14:49:07Z

2

MongoDb (and the date functions in sqlite3) work with a reference date of January 1, 1970 (http://en.wikipedia.org/wiki/Unix_time) which is why you see the results you quote:

sqlite> SELECT datetime(370735200, "unixepoch");
1981-09-30 22:00:00
sqlite> SELECT datetime(360540000, "unixepoch");
1981-06-04 22:00:00

If your data uses a different reference date, you have to offset the values accordingly:

sqlite> SELECT datetime(370735200 + strftime("%s", "2001-01-01"), "unixepoch");
2012-09-30 22:00:00
sqlite> SELECT datetime(360540000 + strftime("%s", "2001-01-01"), "unixepoch");
2012-06-04 22:00:00

(These Timestamps are UTC while yours have a Timezone, that's why there are 1 resp. 2 hours difference to the Timestamps you expect).

So the following formula should work:

var originalDate = Math.round (new Date((data.origDate + 978307200) * 1000))

edited May 27, 2014 at 14:49

answered May 25, 2014 at 18:24

mthierer

6045 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sqlite timestamp to javascript Date object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related