TIME_DIFF
See also DATE_DIFF, DATETIME_DIFF, or TIMESTAMP_DIFF
Definition
The TIME_DIFF
function allows you to find the difference between 2 TIME
s 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
Practical Info
TIME
is earlier than the second one then the output will be negativeTIME
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)
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 TIME
s are indeed TIME
data types, and not DATE
s or DATETIME
s. 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