我正在尝试创建一个查询,仅返回数据以后日期为3天的数据。我尝试了:
date <= DATE_ADD(CURRENT_DATE(), -3, 'DAY')
但这返回Error: Expected INTERVAL expression
请参阅下面的子句
中的子句#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,如果您仍然与Legacy SQL在一起 - 请参见下面的示例
#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
这与字符串格式日期一起工作。
DATE(TIMESTAMP(date)) <= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
刚刚测试并似乎有效。
我添加了此:和日期(Timestamp(dateValue(((&gt; = date_sub(current_date((,间隔21天(
,并设法获得了比过去21天大的记录。我从@ericbrownaustin的代码中更改的唯一一件事是在第二组括号中的第一件代码中更改了"日期"。