DATETIME_DIFF

The DATETIME_DIFF function in BigQuery allows you to find the difference between 2 DATETIMEs in the specified date_part interval.

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, date_part)

Where date_part can be any of the following:

• MICROSECOND

• MILLISECOND

• SECOND

• MINUTE

• DAY

• WEEK : Begins on Sunday

• WEEK(<WEEKDAY>): Begins on <WEEKDAY> where WEEKDAY can be SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.

• ISOWEEK: Uses ISO 8601 week boundaries. ISO weeks begin on Monday.

• MONTH

• QUARTER

• YEAR

• ISOYEAR: Uses the ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.

Returns: INT64

SELECT
  DATETIME_DIFF(CAST('2021-01-01 14:01:05' AS DATETIME), CAST('2021-01-01 12:22:23' AS DATETIME), MINUTE) AS minutes_difference,
  DATETIME_DIFF(CAST('2021-01-01 04:44:33' AS DATETIME), CAST('2020-06-22' AS DATETIME), QUARTER) AS quarter_difference

• If the first DATETIME 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 DATETIME objects would overflow an INT64 value.

A common SQL query WHERE clause is to just pull the data for the last n days. In this case, we can make use of DATETIME_DIFF and CURRENT_DATETIME:

SELECT
  date,
  DATETIME_DIFF(CURRENT_DATETIME(), date, DAY) AS days_since
FROM
  (
    SELECT
      CAST('2021-01-22' AS DATETIME) AS date
    UNION ALL
(    SELECT
      CAST('2020-01-25' AS DATETIME) AS date)
    UNION ALL
(    SELECT
      CAST('2021-01-26' AS DATETIME) AS date)
  ) AS table_3
WHERE
  (DATETIME_DIFF(CURRENT_DATETIME(), date, DAY) <= 30)

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

DATETIME_DIFF(CAST('2020-01-01' as DATETIME),'2021-01-05 03:04:00', DAY) days_diff

• DATE_DIFF

• TIMESTAMP_DIFF

• Dates & Times in Standard SQL