BigQuery doesn't recognize filter-CodePudding

BigQuery doesn't recognize filter over column timestamp and outputs this:

Cannot query over table 'xxxxxx' without a filter over column(s) 'timestamp' that can be used for partition elimination

Query code that produced this message is:

SELECT project as name,
    DATE_TRUNC(timestamp, DAY) as day,
    COUNT (timestamp) as cnt
FROM `xxxxxx` 
WHERE (DATETIME(timestamp) BETWEEN DATETIME_ADD(DATETIME('2022-02-13 00:00:00 UTC'), INTERVAL 1 SECOND)
    AND DATETIME_SUB(DATE_TRUNC(CURRENT_DATETIME(), DAY), INTERVAL 1 SECOND))
GROUP BY 1, 2

CodePudding user response：

Everything works if we switch every conversion to DATETIME and all DATETIME operations with TIMESTAMP format and TIMESTAMP type operations.

SELECT project as name,
    DATE_TRUNC(timestamp, DAY) as day,
    COUNT (timestamp) as cnt
FROM `xxxxxx` 
WHERE (timestamp BETWEEN TIMESTAMP_ADD(TIMESTAMP('2022-02-13 00:00:00 UTC'), INTERVAL 1 SECOND)
    AND TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 1 SECOND))
GROUP BY 1, 2

CodePudding user response：

The table when being created was created with require partition filter set to true. Any query on this table should have a filter on the timestamp.

Refer :- Cannot query over table without a filter that can be used for partition elimination