I have a table in a dataset with the following schema:
date TIMESTAMP id INTEGER ...
The table is partitioned on the date column.
Displaying a preview of the table in the BQ UI reveals it has many rows in February:
date, id, ... 2019-02-19 16:18:00 UTC, 534480012, ... 2019-02-19 16:23:00 UTC, 534423879, ...
However, this query returns zero results:
SELECT id FROM `<project>.<dataset>.<table>` WHERE TIMESTAMP_SUB(date, INTERVAL 60*24 HOUR) <= `date` AND TIMESTAMP_SUB(date, INTERVAL 24 HOUR) >= `date`
(And yes, as of this writing, February rows should show up.)
What’s more is even the “default” query returns zero results:
SELECT id FROM `<project>.<dataset>.<table>` WHERE date = TIMESTAMP("2019-02-19") LIMIT 1000
No errors in either case. Just empty results. What am I doing wrong?
Advertisement
Answer
How can this ever be true?
TIMESTAMP_SUB(date, INTERVAL 24 HOUR) >= `date`
If you subtract 24 hours, then it will be less then date rather than bigger than date.
As for your second query, you simply have no timestamps that are exactly at midnight. Presumably, you intend something like:
WHERE DATE(date) = DATE('2019-02-19')
I strongly recommend that you change the name of the column. Naming a column after a SQL keyword is a bad idea. Calling something a “date” when it is really a timestamp is misleading and confusing.