timestamp format in google bigquery loses

Question

As per another question, i've been doing this (python) to push my timestamps into bigquery (they come from a node-js app in a nr-of-miliseconds format):

e["createdOn"] = e["createdOn"] / 1000.0

But they end up as this:

SELECT updatedOn,TIMESTAMP_TO_USEC(updatedOn) FROM [table.sessions] WHERE session = xxxxxxx
Row updatedOn f0_    
1 2014-08-18 11:55:49 UTC 1408362949426000
2 2014-08-18 11:55:49 UTC 1408362949426000

I've been printing debug information, and this is their last form before being inserted with insertAll:

{u'session': 100000000000080736, u'user': 1000000000075756, u'updatedOn': 1409052076.223}

No, more like 1409052076.223 seems to become 1409052076.000 which has a TIMESTAMP_TO_USEC representation of 1409052076000. — user37203, Aug 26 '14 at 13:47
Check again, on my tests 1409052076.223 becomes 1409052076223000 with TIMESTAMP_TO_USEC, no precision lost (with 3 extra zeros as USEC is different to MSEC) — Felipe Hoffa, Aug 26 '14 at 17:57

score 2 · Accepted Answer · answered Aug 26 '14 at 16:59

I think you're confusing USEC (microseconds) and MSEC (milliseconds). You're providing the timestamp in milliseconds, but you're then converting to microseconds, which will have the last three digits as 0 because that is higher precision than you provided.

If you use the TIMESTAMP_TO_MSEC function instead, it should do what you expect.

timestamp format in google bigquery loses

1 Answers1