21 Oct 18 · dan-irving · #bigquery ·   Bookmark   ×

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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied