Link Search Menu Expand Document

DATE_DIFF

Calculates the difference between start_date and end_date by the indicated unit.

Syntax

DATE_DIFF('<unit>', <start_date>, <end_date>)

Parameters

Parameter Description
<unit> A unit of time. This can be any of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEk, MONTH, QUARTER, YEAR, DECADE, CENTURY.
<start_date> An expression that evaluates to a DATE, TIMESTAMP or TIMESTAMPTZ value.
<end_date> An expression that evaluates to a DATE, TIMESTAMP or TIMESTAMPTZ value.

Return Types

LONG

Example

The example below uses a table date_test with the columns and values below.

Category sale_date sale_timestamp
a 2012-05-01 2017-06-15 09:34:21
b 2021-08-30 2014-01-15 12:14:46
c 1999-12-31 1999-09-15 11:33:21
SELECT
	category,
	DATE_DIFF('YEAR', sale_date, sale_datetime) AS year_difference
FROM
	date_test;

Returns:

+----------+-----------------+
| Category | year_difference |
| a        | 5               |
| b        | -7              |
| c        | 0               |
+----------+-----------------+

This example below finds the number of days difference between two date strings. The strings first need to be transformed to TIMESTAMP type using the CAST function.

SELECT
	DATE_DIFF(
		'day',
		CAST('2020/08/31 10:00:00' AS TIMESTAMP),
		CAST('2020/08/31 11:00:00' AS TIMESTAMP)
	);

Returns: 0