How to Query Date and Time in BigQuery

BigQuery offers robust support for working with DATE, TIME, DATETIME, and TIMESTAMP data types. This guide explains how to query these types effectively, with practical examples.

1. Understand BigQuery Date and Time Types

• DATE — calendar date (YYYY-MM-DD)
• TIME — time of day without date or timezone (HH:MM:SS)
• DATETIME — date + time without timezone
• TIMESTAMP — date + time with UTC timezone

2. Basic Date Query

SELECT name, birth_date 
FROM `my_dataset.my_table` 
WHERE birth_date = '2023-01-01';

3. Filter by Date Range

SELECT order_id, order_date 
FROM `my_dataset.orders` 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

4. Extract Parts of Date or Time

SELECT 
  EXTRACT(YEAR FROM order_date) AS year,
  EXTRACT(MONTH FROM order_date) AS month
FROM `my_dataset.orders`;

5. Convert Between Types

SELECT 
  DATETIME(TIMESTAMP '2024-12-31 23:59:59 UTC') AS dt,
  DATE(TIMESTAMP '2024-12-31 23:59:59 UTC') AS date_only
;

6. Work with Current Date/Time

SELECT 
  CURRENT_DATE() AS today,
  CURRENT_TIMESTAMP() AS now,
  CURRENT_DATETIME() AS datetime_now
;

7. Format Dates and Times

SELECT 
  FORMAT_DATE('%B %d, %Y', DATE '2024-12-31') AS formatted_date,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP()) AS formatted_timestamp
;

8. Time Zone Conversion

SELECT 
  TIMESTAMP('2024-12-31 23:59:59 UTC', 'America/New_York') AS ny_time
;

Pro Tips

• Always check the data type in your table schema.
• Use SAFE_CAST() when converting to avoid errors.
• Use PARSE_DATE(), PARSE_TIMESTAMP() to convert strings.

Final Thoughts

BigQuery’s date and time functions let you write powerful time-based queries. Practice with sample queries and test with your own datasets to master them!