CODEWITHSUNDEEP
google-bigquery
CacsAntibis
CacsAntibis

Reputation: 35

select 10 day old entries BigQuery

I need to get only entries that are 10 days + old in BigQuery, I went to other questions and google doc (which is confuse as hell) but could not find a the result I expected.

My query:

SELECT Id
FROM myTable
WHERE eventTS > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY)

The this query is returning values that are not 10 days + old.

when I add -10 DAY doesn't return nothing.

Any help is MUCH welcome!

Thanks in advance.

Upvotes: 1

Views: 73

Answers (2)

Vibhor Gupta
Vibhor Gupta

Reputation: 699

Try following Bigquery functions SELECT CURRENT_TIMESTAMP() as Curr_Ts, TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY) as Sub_Ts , TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -10 DAY) as Add_Ts It will Add & Substract 10 days from CURRENT_TIMESTAMP(). Output will be as :-

enter image description here

Your Query will get modified as :-

SELECT Id
FROM myTable
WHERE eventTS < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY)

Or

SELECT Id
FROM myTable
WHERE eventTS < TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -10 DAY)

Upvotes: 1

Sergey Geron
Sergey Geron

Reputation: 10152

Try replacing > with <:

SELECT Id
FROM myTable
WHERE eventTS < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY)

Upvotes: 0

Related Questions