CODEWITHSUNDEEP
javascriptsqlitedate
Rubs
Rubs

Reputation: 829

Sqlite timestamp to javascript Date object

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.

Upvotes: 0

Views: 3895

Answers (1)

mthierer
mthierer

Reputation: 604

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))

Upvotes: 2

Related Questions