CODEWITHSUNDEEP
google-bigquery
ericbrownaustin
ericbrownaustin

Reputation: 1300

BigQuery Where Date is Less Than or Equal to 3 Days Minus Current Date

I'm trying to create a query to only return data where date is minus 3 days from the current date. I've tried:

date <= DATE_ADD(CURRENT_DATE(), -3, 'DAY')

But this returns Error: Expected INTERVAL expression

Upvotes: 16

Views: 48666

Answers (3)

Ash Parmar
Ash Parmar

Reputation: 1

Just tested this and seems to work.

I added this : and DATE(TIMESTAMP(datevalue)) >= DATE_SUB(CURRENT_DATE(), INTERVAL 21 DAY)

and managed to get all records greater than last 21 days worth. Only thing I changed from @ericbrownaustin 's code was changed the 'date' in the first piece of code in the second set of parenthesis.

Upvotes: 0

ericbrownaustin
ericbrownaustin

Reputation: 1300

This works with a string formatted date.

DATE(TIMESTAMP(date)) <= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)

Upvotes: 8

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

See WHERE clause in below example 

#standardSQL
WITH yourTable AS (
  SELECT i, date
  FROM UNNEST(GENERATE_DATE_ARRAY('2017-04-15', '2017-04-28')) AS date WITH OFFSET AS i
)
SELECT *
FROM yourTable
WHERE date <= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
-- ORDER BY date

Btw, in case if you are still with Legacy SQL - see below example 

#legacySQL
SELECT *
FROM -- yourTable  
  (SELECT 1 AS id, DATE('2017-04-20') AS date),
  (SELECT 2 AS id, DATE('2017-04-21') AS date),
  (SELECT 3 AS id, DATE('2017-04-22') AS date),
  (SELECT 4 AS id, DATE('2017-04-23') AS date),
  (SELECT 5 AS id, DATE('2017-04-24') AS date),
  (SELECT 6 AS id, DATE('2017-04-25') AS date)
WHERE TIMESTAMP(date) <= DATE_ADD(TIMESTAMP(CURRENT_DATE()), -3, 'DAY')
-- ORDER BY date

Upvotes: 21

Related Questions