CODEWITHSUNDEEP
sqlgoogle-bigquerylooker-studio
DimitFromCave
DimitFromCave

Reputation: 143

With bigquery variable @DS_END_DATE I am losing 24 hours

So, there is a piece of query from bigquery to google data studio report:

WHERE 
   creation_date IN (NULL, '1970-01-01T00:00:00') OR 
   creation_date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND
   PARSE_DATE('%Y%m%d', @DS_END_DATE)

And @DS_END_DATE helps users select date range in GDS report. However, when filtering data, I am missing last day. For example, if @DS_END_DATE is '2022-10-20', I can't find rows with creation_date = '2022-10-20T14:03:08' in report. But report contains all data till '2022-10-20'

How can I query bigquery to get all stuff from @DS_END_DATE like it is '2022-10-20T23:59:59' ?

P.S. Yeah, there were some troubles first time, so I am using creation_date IN (NULL, '1970-01-01T00:00:00') to identify deleted IDs, but it doesn't matter.

Upvotes: 0

Views: 233

Answers (1)

Daniel Zagales
Daniel Zagales

Reputation: 3034

When BigQuery is doing the comparison it is normalizing the data types and casting your provided date as a timestamp. When it does this conversion it looks like 2022-10-20 00:00:00 UTC. Given that you can see why it is dropping things on 2022-10-20. To alleviate this you can do something like:

select 
creation_date
from sample_data
where cast(creation_date as date) between '2022-10-19' and '2022-10-20'

Upvotes: 1

Related Questions