BigQuery where date is less than or equal to 3 days minus the current date
I am trying to create a query to return data only where the date is minus 3 days from the current date. I tried:
date <= DATE_ADD(CURRENT_DATE(), -3, 'DAY')
But this returns Error: Expected INTERVAL expression
+3
ericbrownaustin
source
to share
2 answers
See the WHERE clause in the example below
#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 if you are still using 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
+5
Mikhail Berlyant
source
to share
This works with a date formatted string.
DATE(TIMESTAMP(date)) <= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
+1
ericbrownaustin
source
to share