How to Calculate Date Difference in BigQuery

How do I calculate the difference between two dates in BigQuery?

DATE_DIFF / DATETIME_DIFF / TIMESTAMP_DIFF return the numeric interval between two dates, datetimes, or timestamps in BigQuery.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is the fastest way to get the difference between two dates in BigQuery?

Use the DATE_DIFF, DATETIME_DIFF, or TIMESTAMP_DIFF functions. They subtract a start value from an end value and return the result in the unit you choose—seconds, minutes, hours, days, weeks, months, quarters, or years.

How does DATE_DIFF work syntactically?

Pass three arguments: DATE_DIFF(end_date, start_date, DATE_PART). DATE_PART is a string literal such as 'DAY' or 'MONTH'. The function always returns an INT64.

Which BigQuery function should I use—DATE, DATETIME, or TIMESTAMP?

Match the data type of your columns. For DATE columns use DATE_DIFF. For combined date-time use DATETIME_DIFF. For timezone-aware timestamps, use TIMESTAMP_DIFF.

Can I calculate a customer’s repeat-purchase window?

Yes. Join Orders to itself, sort by order_date, and call DATE_DIFF on consecutive orders. This reveals how many days pass between purchases.

Best practice: filter nulls first

Always filter out NULL start or end values. DATE_DIFF on NULL returns NULL, which may hide errors in downstream metrics.

Best practice: validate units

Store units in a comment or alias column so analysts know whether a metric is in days or months. Consistency prevents misinterpretation during reporting.

Why How to Calculate Date Difference in BigQuery is important

How to Calculate Date Difference in BigQuery Example Usage


-- How many hours did each product stay out of stock?
WITH stockouts AS (
  SELECT p.id AS product_id,
         prev.stock AS previous_stock,
         p.stock   AS current_stock,
         LAG(p.stock) OVER(PARTITION BY p.id ORDER BY p.updated_at) AS prior_stock,
         p.updated_at
  FROM   `ecommerce.Products` AS p
)
SELECT product_id,
       TIMESTAMP_DIFF(updated_at, LAG(updated_at) OVER w, HOUR) AS hours_out_of_stock
FROM   stockouts
WINDOW w AS (PARTITION BY product_id ORDER BY updated_at)
WHERE  prior_stock > 0 AND current_stock = 0;

How to Calculate Date Difference in BigQuery Syntax


DATE_DIFF(end_date, start_date, part)
DATETIME_DIFF(end_datetime, start_datetime, part)
TIMESTAMP_DIFF(end_timestamp, start_timestamp, part)

part: 'MICROSECOND' | 'MILLISECOND' | 'SECOND' | 'MINUTE' | 'HOUR' | 'DAY' | 'WEEK' | 'MONTH' | 'QUARTER' | 'YEAR'

-- Example: days between order placement and customer creation
SELECT c.id AS customer_id,
       DATE_DIFF(o.order_date, c.created_at, 'DAY') AS days_to_first_order
FROM   `ecommerce.Customers` AS c
JOIN   `ecommerce.Orders`    AS o
ON     o.customer_id = c.id
WHERE  o.order_date IS NOT NULL;

Common Mistakes

Using mismatched data types. Calling DATE_DIFF on TIMESTAMP columns implicitly casts to UTC date, causing off-by-one errors near midnight. Fix by using TIMESTAMP_DIFF or casting explicitly.
Forgetting the unit argument. Omitting the third parameter defaults to DAY in legacy SQL but raises an error in standard SQL. Always specify the unit string explicitly.