CODEWITHSUNDEEP
sqldatetimegoogle-bigquery
maximelian1986
maximelian1986

Reputation: 2452

BigQuery compare Date

In BigQuery table what I using some date stored in DateTime typed columns with value like "2016-01-20T00:00:00". I want to run query that for example show row which included in some range (lets say from 2016-01-01 to 2016-02-28).

So problem is that when I put

...Where data < TIMESTAMP('2017-01-30 00:00:00')...//less then

it show me nothing but when I change it to opposite value like

...Where data > TIMESTAMP('2017-01-30 00:00:00')...//greater then

it returns me even those values that logically should be excluded (like 2017-01-20)

I have tested that TIMESTAMP('2017-01-20 00:00:00') returning '2017-01-26 00:00:00 UTC'.

So which method from bigQuery DateTime I should use to be able to compare dates that I have? May be I need to convert both of them into some kind 'milliseconds since' value?

Thanks

Upvotes: 16

Views: 68523

Answers (2)

maximelian1986
maximelian1986

Reputation: 2452

Thank you all for help. Your answers and link has leaded me to solution.

where data<cast('2017-01-23 00:00:00' as datetime)

cast('2017-01-23 00:00:00' as datetime) provide '2017-01-23T00:00:00' value and bigQuery are happy with it and give me wanted result.

Upvotes: 23

cloudsafe
cloudsafe

Reputation: 2504

If your column is of datetime int:

Where data < 20170130

and 

Where data > 20170130

If your column is of datetime datatype, drop the TIMESTAMP:

Where data < '2017-01-30 00:00:00'

and 

Where data > '2017-01-30 00:00:00'

Upvotes: 2

Related Questions