Select from a timestamp column between now and n days ago

Consider, if you need to filter records based on x days or weeks before today. Example query Select all records which are 2 weeks old.

You can use timestamp functions to achieve this:

SELECT t.*
FROM `my-pj.my_dataset.sample_table` t
WHERE register_date BETWEEN TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -150 DAY) 
AND CURRENT_TIMESTAMP())
LIMIT 10;

BigQuery has three data types for date/time values: date, datetime, and timestamp. These are not mutually interchangeable. The basic idea is:

• Dates have no time component and no timezone
• Datetimes have a time component and no timezone
• Timestamp has both a time component and a timezone. In fact, it represents the value in UTC

dan-irving

posted on 21 Oct 18