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?

score 11 · Accepted Answer · answered May 17 '14 at 15:55

11

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.

answered May 17 '14 at 15:55

Felipe Hoffa

54,922
16
151
325

Seems to be the issue. Thanks much. – mungojerie May 17 '14 at 16:36
To add to this answer, javascript Date "getTime() method returns the number of milliseconds* since the Unix Epoch." And Timestamp uses the number of seconds since the Unix Epoch. – Gaunt Mar 29 '20 at 21:52

score 2 · Answer 2 · answered May 15 '18 at 10:31

2

Use:

bigquery.datetime(new Date().toISOString())

answered May 15 '18 at 10:31

Pranay singh

135
1
5

3

Please edit your answer with more detail: How does this solve the OP's problem? How is it useful when compared to the accepted answer? – O.O.Balance May 15 '18 at 10:55
When Googling for a solution to this issue, this suggestion worked for me. – readikus Mar 28 '23 at 18:26

score 2 · Answer 3 · answered Oct 07 '22 at 11:04

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.

answered Oct 07 '22 at 11:04

shary.sharath

649
2
14
29

BigQuery / Node.js timestamp way off

3 Answers3

Linked