TIMESTAMP_DIFF

See also DATE_DIFF, DATETIME_DIFF, or TIME_DIFF

Definition

The TIMESTAMP_DIFF function allows you to find the difference between 2 TIMESTAMPs in the specified date_part interval.

Syntax

TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, date_part)

Where date_part can be any of the following:

Returns: INT64

SELECT
  TIMESTAMP_DIFF(CAST('2021-01-01 14:01:05' AS TIMESTAMP), CAST('2021-01-01 12:22:23' AS TIMESTAMP), MINUTE) AS minutes_difference,
  TIMESTAMP_DIFF(CAST('2021-01-01 01:44:33' AS TIMESTAMP), CAST('2020-12-31 22:04:60' AS TIMESTAMP), MILLISECOND) AS millisecond_difference

Practical Info

Common Questions

How to filter for the last n seconds?

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

SELECT
  date,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), date, SECOND) AS seconds_since
FROM
  (
    SELECT
      TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 SECOND) AS date
    UNION ALL
(    SELECT
      TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 SECOND) AS date)
    UNION ALL
(    SELECT
      TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 SECOND) AS date)
  ) AS table_3
WHERE
  (TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), date, SECOND) <= 30)

Troubleshooting Common Errors

Argument 2 of TIMESTAMP_DIFF has incorrect type: expected timestamp found datetime.

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

TIMESTAMP_DIFF(CAST('2020-01-01 03:22:01' as TIMESTAMP),'2021-01-05 03:04:00', DAY) days_diff

Related Pages