DATETIME_DIFF

You might also want to see DATE_DIFF, TIMESTAMP_DIFF, OR TIME_DIFF

Definition

The DATETIME_DIFF function in BigQuery allows you to find the difference between 2 DATETIMEs in the specified date_part interval.

Syntax

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, date_part)

Where date_part can be any of the following:

Returns: INT64

SELECT
  DATETIME_DIFF(CAST('2021-01-01 14:01:05' AS DATETIME), CAST('2021-01-01 12:22:23' AS DATETIME), MINUTE) AS minutes_difference,
  DATETIME_DIFF(CAST('2021-01-01 04:44:33' AS DATETIME), CAST('2020-06-22' AS DATETIME), QUARTER) AS quarter_difference

Practical Info

Common Questions

How to filter for the last n days?

A common SQL query WHERE clause is to just pull the data for the last n days. In this case, we can make use of DATETIME_DIFF and CURRENT_DATETIME:

SELECT
  date,
  DATETIME_DIFF(CURRENT_DATETIME(), date, DAY) AS days_since
FROM
  (
    SELECT
      CAST('2021-01-22' AS DATETIME) AS date
    UNION ALL
(    SELECT
      CAST('2020-01-25' AS DATETIME) AS date)
    UNION ALL
(    SELECT
      CAST('2021-01-26' AS DATETIME) AS date)
  ) AS table_3
WHERE
  (DATETIME_DIFF(CURRENT_DATETIME(), date, DAY) <= 30)

Troubleshooting Common Errors

Argument 2 of DATETIME_DIFF has incorrect type: expected datetime found date.

This one is all too common. If you're using DATETIME_DIFF, you'll need to make sure both of your datetimes are indeed DATETIME data types, and not DATEs or TIMESTAMPs. It's usually easy enough to add a CAST(datetime_col as DATETIME) to your function:

DATETIME_DIFF(CAST('2020-01-01' as DATETIME),'2021-01-05 03:04:00', DAY) days_diff

Related Pages