SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='timestamp-diff'

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.

Where date_part can be any of the following: 

TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, date_part)
  • MICROSECOND
  • MILLISECOND

Returns: INT64

minutes_differencemillisecond_difference
9813173000

Practical Info

  • If the first TIMESTAMP 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 TIMESTAMP 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 TIMESTAMP_DIFF and CURRENT_TIMESTAMP:

Loading code...
dateseconds_since
2021-05-13T11:50:41.110Z30
2021-05-13T11:51:01.110Z10

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:

Loading code...
  • DATETIME_DIFF
  • Dates & Times in Standard SQL
Loading code...