BigQuery query on (custom) timestamp partitioned table returns zero results

I have a table in a dataset with the following schema:

date TIMESTAMP
id INTEGER
...

​x
 
date TIMESTAMPid 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, ...

 
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`

 
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

 
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?

Answer

How can this ever be true?

TIMESTAMP_SUB(date, INTERVAL 24 HOUR) >= `date`

 
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')

 
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.

Advertisement

Answer