BigQuery / Node.js timestamp way off

Question

Doing a streaming insert into Google BigQuery, from a light Node.js app, using this package: https://www.npmjs.org/package/bigquery

I generated a timestamp on my server via this simple line of code:

jsonData['createdAt'] = new Date().getTime();

I then insert that into BigQuery, into a field with type 'timestamp'. There is no intermediate step (besides the Node package).

But many, although not all, of dates look waaaaaay off. For example:

46343-08-28 05:58:59 UTC

When that should say something like 11:45pm on 05-16-2014. However, some of my createdAt dates are correct, and I can't find a reason for the difference.

Any suggestions?

Answer 1

Without actually debugging the JS code, this seems to be an "off by a thousand" problem.

Check this out:

SELECT USEC_TO_TIMESTAMP(1400341611711851)
2014-05-17 15:46:51 UTC

SELECT USEC_TO_TIMESTAMP(1400341611711851*1000)
46345-01-22 13:01:51 UTC

SELECT MSEC_TO_TIMESTAMP(1400341611711851)
46345-01-22 13:01:51 UTC

SELECT MSEC_TO_TIMESTAMP(1400341611711851/1000)
2014-05-17 15:46:51 UTC

So to get a UNIX timestamp in seconds, divide the new Date().getTime() number by 1000.

Answer 2

I had a requirement where I am supposed to send Timestamp type to BigQuery table from NodeJS. I did it as follows:

bigqueryClient.timestamp(new Date(Date.now()))

I hope this may help someone.