CODEWITHSUNDEEP
google-bigquery
john2x
john2x

Reputation: 23634

Inconsistent TIMESTAMP results in BigQuery browser tool?

I'm trying to query a table on a timestamp field, but I'm getting some strange results.

For example, to query rows with timestamp > 1402913235 (which is 6 Jun 2014 10:07:15 GMT according to http://www.epochconverter.com) returns the ff:

SELECT timestamp FROM [MyTable]
where timestamp > 1402913235 
order by timestamp LIMIT 20;

Results:
--------
Row timestamp    
1   2009-02-13 23:31:30 UTC  
2   2009-02-13 23:31:30 UTC  
3   2009-02-13 23:31:30 UTC  
4   2009-02-13 23:31:30 UTC  
5   2009-02-13 23:31:35 UTC

Downloaded Raw CSV:
-------------------
timestamp
1.23456789E9
1.23456789E9
1.23456789E9
1.23456789E9
1.234567895E9

Clearly, the first 5 results are not greater than 1402913235.

Another example with the same table:

SELECT timestamp FROM [MyTable]
order by timestamp desc LIMIT 20;

Results:
--------
Row timestamp    
1   46426-07-19 16:15:18 UTC     
2   46426-07-19 14:45:44 UTC     
3   46426-07-19 14:44:40 UTC     
4   46426-07-19 14:34:12 UTC     
5   46426-07-19 09:17:21 UTC

Downloaded Raw CSV:
-------------------
timestamp
1.4029131465180002E12
1.402913141144E12
1.40291314108E12
1.402913140452E12
1.402913121441E12

Again, according to epochconverter.com, The first result should be 1.4029131465180002E12 == 1402913146518.0002 == 16 Jun 2014 10:05:46 GMT and not 46426-07-19 16:15:18

What's going on?

Upvotes: 0

Views: 207

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

Probably off by a thousand.

See this:

SELECT USEC_TO_TIMESTAMP(1402913146518) as_usec, 
       MSEC_TO_TIMESTAMP(1402913146518) as_msec,
       SEC_TO_TIMESTAMP(1402913146518) as_sec

as_usec                  as_msec                    as_sec   
1970-01-17 05:41:53 UTC  2014-06-16 10:05:46 UTC    46426-07-19 16:15:18 UTC

For the first question, remember to transform to timestamp before comparing:

SELECT 
  as_msec > (1402913146518+100) timestamp_more_than_number,
  as_msec > MSEC_TO_TIMESTAMP(1402913146518+100) timestamp_more_than_timestamp
FROM (SELECT MSEC_TO_TIMESTAMP(1402913146518) as_msec)

timestamp_more_than_number  timestamp_more_than_timestamp    
true                        false

Upvotes: 4

Related Questions