Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 37 ยท Querying and Functions

Dates, Times and Time Zones in BigQuery

Work with dates and timestamps in BigQuery: CURRENT_DATE, DATE_TRUNC, DATE_ADD, DATE_DIFF, EXTRACT, FORMAT_TIMESTAMP, PARSE_DATE and time zone conversion, with SQLite stand-ins.

Almost every analytics question has a time dimension: orders per week, retention after 30 days, revenue this quarter against last. BigQuery has a rich set of date and time functions, and a clear rule for time zones. This lesson covers the ones you will reach for every day: truncating, adding, subtracting, extracting, formatting and parsing.

The functions are BigQuery-only, so the headline examples are read-only. SQLite's date functions are different (date(), strftime(), text-based), so the runnable stand-ins teach the same thinking with SQLite spellings, and I put the BigQuery spelling beside each.

Current date and time

SQL
SELECT
  CURRENT_DATE()      AS today_utc,
  CURRENT_DATE('Asia/Tokyo') AS today_tokyo,
  CURRENT_TIMESTAMP() AS now_utc,
  CURRENT_DATETIME()  AS now_wall_clock;

CURRENT_DATE() uses UTC unless you pass a time zone. In a scheduled query a CURRENT_* function returns a fixed value for the whole query, so all rows see the same "now".

Truncating to a period

DATE_TRUNC snaps a date to the start of a period. It is the foundation of "per week" or "per month" reports:

SQL
SELECT
  DATE_TRUNC(order_date, WEEK(MONDAY)) AS week_start,
  DATE_TRUNC(order_date, MONTH)        AS month_start,
  COUNT(*) AS orders
FROM `my-project.shop.orders`
GROUP BY week_start, month_start;

Periods include DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR. For timestamps use TIMESTAMP_TRUNC(ts, HOUR) and for datetimes DATETIME_TRUNC. In SQLite the first-of-month idea is a modifier:

SQL Stand-in: orders per month ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Adding and subtracting

SQL
SELECT
  DATE_ADD(DATE '2025-01-31', INTERVAL 1 MONTH)  AS plus_month,   -- 2025-02-28
  DATE_SUB(DATE '2025-03-01', INTERVAL 7 DAY)    AS minus_week,
  TIMESTAMP_ADD(TIMESTAMP '2025-03-01 10:00:00+00', INTERVAL 90 MINUTE) AS later;

Month arithmetic clamps to the end of a short month (January 31 plus one month is February 28). DATE_ADD and DATE_SUB accept DAY, WEEK, MONTH, QUARTER and YEAR; for timestamps use TIMESTAMP_ADD and TIMESTAMP_SUB with units down to microseconds.

SQL Stand-in: date arithmetic ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

(SQLite normalises overflow differently: January 31 plus one month becomes March 3 here, not February 28. That is one reason to read each database's date rules rather than assume.)

Differences

DATE_DIFF(later, earlier, unit) counts boundaries crossed, not full units:

SQL
SELECT
  DATE_DIFF(DATE '2025-03-01', DATE '2025-02-27', DAY)   AS days,        -- 2
  DATE_DIFF(DATE '2025-03-01', DATE '2025-02-27', MONTH) AS months,      -- 1: crossed a month boundary
  TIMESTAMP_DIFF(TIMESTAMP '2025-03-01 12:00:00+00',
                 TIMESTAMP '2025-03-01 09:30:00+00', MINUTE) AS minutes;  -- 150

Note the argument order: later first. Mixing it up gives negative numbers. For MONTH the difference counts calendar-month changes, so it is not "complete months elapsed".

SQL Stand-in: days between two dates ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Extracting parts

SQL
SELECT
  EXTRACT(YEAR FROM order_date)      AS yr,
  EXTRACT(MONTH FROM order_date)     AS mo,
  EXTRACT(DAYOFWEEK FROM order_date) AS dow,   -- 1 = Sunday
  EXTRACT(ISOWEEK FROM order_date)   AS wk
FROM `my-project.shop.orders`;

DAYOFWEEK starts on Sunday (1 to 7). FORMAT_DATE('%A', d) gives the weekday name instead.

SQL Stand-in: weekday and month parts ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Formatting and parsing

SQL
SELECT
  FORMAT_DATE('%d %B %Y', DATE '2025-03-01')            AS pretty,      -- 01 March 2025
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', ts, 'Europe/Paris') AS paris_time,
  PARSE_DATE('%d/%m/%Y', '31/12/2025')                  AS parsed,
  SAFE.PARSE_DATE('%d/%m/%Y', '31/02/2025')             AS invalid_is_null,
  PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2025-03-01 08:15:00') AS parsed_ts
FROM `my-project.app.events`;

Format codes are the C-style ones: %Y year, %m month, %d day, %H hour (24h), %M minute, %S second, %B month name, %A weekday name. Use SAFE.PARSE_* for untrusted input.

Time zones

A TIMESTAMP is an instant; showing it as a date needs a zone. The rules:

  1. Store events as TIMESTAMP (UTC).
  2. Convert at query time, using IANA names such as 'America/New_York', never fixed offsets that ignore daylight saving.
  3. Group by the business's zone, so that "daily revenue" matches the day the finance team sees.
SQL
SELECT
  DATE(created_at, 'America/Los_Angeles') AS local_day,
  COUNT(*) AS orders
FROM `my-project.shop.orders`
GROUP BY local_day;

Generating a calendar and filling gaps

Reports often must show days with zero orders. Join to a generated calendar:

SQL
SELECT d AS day, COALESCE(o.orders, 0) AS orders
FROM UNNEST(GENERATE_DATE_ARRAY('2025-01-01', '2025-01-31')) AS d
LEFT JOIN (
  SELECT DATE(created_at) AS day, COUNT(*) AS orders
  FROM `my-project.shop.orders`
  GROUP BY day
) AS o ON o.day = d;

The portable pattern, runnable in SQLite with a recursive CTE:

SQL Stand-in: fill empty days with zero ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function snaps a date to the first day of its month?

    DATE_TRUNC returns the start of the chosen period.
  2. 2. What does DATE_DIFF(DATE '2025-03-01', DATE '2025-02-27', MONTH) return?

    DATE_DIFF counts month boundaries crossed, and one was crossed between 27 February and 1 March.
  3. 3. How should you convert a UTC TIMESTAMP into a local calendar day?

    Named zones handle daylight saving correctly.
  4. 4. Why use SAFE.PARSE_DATE for user-supplied text?

    The SAFE prefix converts errors into NULL values.

Summary

  • DATE_TRUNC, DATE_ADD, DATE_SUB, DATE_DIFF, EXTRACT and FORMAT_*/PARSE_* cover most needs.
  • DATE_DIFF takes the later date first and counts boundaries crossed.
  • Store TIMESTAMP in UTC; convert with named time zones when reporting.
  • Join to a generated calendar to show empty days.

Next: Strings, Regex and Text Functions