SQL Resources
SQL
TIME_DIFF

TIME_DIFF

See also DATE_DIFF, DATETIME_DIFF, or TIMESTAMP_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_timesecond_timedifference
    15:30:0014:35:0055

    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)
    timeseconds_since
    13:05:00-2336
    13:05:00-2336
    13:05:00-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

  • DATE_DIFF
  • DATETIME_DIFF
  • Dates & Times in Standard SQL
  • Subscribe to newsletter

    Subscribe to receive the latest blog posts to your inbox every week.

    By subscribing you agree to our Privacy Policy.
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.

    Start solving your organization's biggest problems with Count today.

    Stay up to date with all things count, data and problem solving.