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?

Accepted Answer

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.

BigQuery / Node.js timestamp way off

Solution

Problem