SQL Resources/BigQuery/TIME_DIFF

TIME_DIFF

Definition

The TIME_DIFF function allows you to find the difference between 2 TIMEs in the specified part interval.

Syntax

TIME_DIFF(time_expression_a, time_expression_b, part)

Where part can be any of the following:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

Returns: INT64

SELECT
  TIME '15:30:00' AS first_time,
  TIME '14:35:00' AS second_time,
  TIME_DIFF(TIME '15:30:00' , TIME '14:35:00', MINUTE) AS difference
first_time
15:30:00
second_time
14:35:00
difference
55

Practical Info

  • If the first TIME is earlier than the second one then the output will be negative
  • Throws an error if the computation overflows the result type, such as if the difference in microseconds between the two TIME objects would overflow an INT64 value.

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 TIME_DIFF and CURRENT_TIME:

SELECT
  time,
  TIME_DIFF(CURRENT_TIME(), time, SECOND) AS seconds_since
FROM
  (
    SELECT
      TIME '13:05:00' AS time
    UNION ALL
(    SELECT
      TIME '13:05:00' AS time)
    UNION ALL
(    SELECT
      TIME '13:05:00' AS time)
  ) AS table_3
WHERE
  (TIME_DIFF(CURRENT_TIME(), time, SECOND) <= 30)
time
13:05:00
13:05:00
13:05:00
seconds_since
-2336
-2336
-2336

Troubleshooting Common Errors

Argument 2 of TIME_DIFF has incorrect type: expected time found datetime.

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

TIME_DIFF(CAST('2020-01-01 03:22:01' as TIME),'03:04:00', HOUR) hours_diff

Related Pages

Try a better way to write SQL in BigQuery
Write SQL together with Count.
Try a better way to write SQL in BigQuery
Write SQL together with Count.