SQL Tutorials
Understanding FROM
Understanding JOIN
Understanding WHERE
Using dates in SQL
Using strings in SQL
SQL Functions
CASE
COUNT
BigQuery Tutorials
Arrays Explained
Data Types
Dates and Times
String Functions Explained
Window Functions Explained
BigQuery Functions
ARRAY_AGG
CASE
CAST
COALESCE
CONCAT
COUNT [DISTINCT]
CURRENT_DATE
CURRENT_DATETIME
CURRENT_TIMESTAMP
DATE_DIFF
DATE_TRUNC
DATETIME_DIFF
DATETIME_TRUNC
EXTRACT
IF
MEDIAN
SUBSTR
TIME_DIFF
TIME_TRUNC
TIMESTAMP_DIFF
TIMESTAMP_TRUNC
UNNEST
Snowflake Tutorials
Histograms
Linear Regression
Moving averages
Pivot Tables
Running Totals
Summary Statistics
Window Functions
SQL Server Tutorials
Data Types
Dates and Times
SQL Resources/BigQuery/TIMESTAMP_DIFF

TIMESTAMP_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:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • DAY

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
minutes_difference
98
millisecond_difference
13173000

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: 

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)
date
2021-05-13T11:50:41.110Z
2021-05-13T11:51:01.110Z
seconds_since
30
10

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

Become a SQL Expert
Get five free SQL lessons from Count's Head of Data, Taylor Brownlow.
Email
Title