Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part. The function returns the result of subtracting the second argument from the third argument.
Alternative for DATEDIFF.
TIMEDIFF( <date_or_time_part> , <date_or_time_expr1> , <date_or time_expr2> )
The unit of time. Must be one of the values listed in Supported Date and Time Parts (e.g.
month). The value can be a string literal or can be unquoted (e.g.
The values to compare. Must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. The value
date_or_time_expr1is subtracted from
Returns an integer representing the number of units (seconds, days, etc.) difference between
Output values can be negative, for example, -12 days.
date_or_time_expr2can be a date, time, or timestamp.
The function supports units of years, quarters, months, weeks, days, hours, minutes, seconds, milliseconds, microseconds, and nanoseconds.
date_or_time_partmust be one of the values listed in Supported Date and Time Parts.
The unit (e.g.
month) used to calculate the difference determines which parts of the DATE, TIME, or TIMESTAMP field are used to determine the result and thus determines the precision of the result.
Smaller units are not used, so values are not rounded. For example, even though the difference between January 1, 2021 and February 28, 2021 is closer to two months than to one month, the following returns one month:
DATEDIFF(month, '2021-01-01'::DATE, '2021-02-28'::DATE)
For a DATE value:
yearuses only the year and disregards all the other parts.
monthuses the month and year.
dayuses the entire date.
For a TIME value:
houruses only the hour and disregards all the other parts.
minuteuses the hour and minute.
seconduses the hour, minute, and second, but not the fractional seconds.
milliseconduses the hour, minute, second, and first three digits of the fractional seconds. Fractional seconds are not rounded. For example,
DATEDIFF(milliseconds, '00:00:00', '00:00:01.1239')returns 1.123 seconds, not 1.124 seconds.
microseconduses the hour, minute, second, and first six digits of the fractional seconds. Fractional seconds are not rounded.
nanoseconduses the hour, minute, second, and all nine digits of the fractional seconds.
For a TIMESTAMP value:
The rules match the rules for DATE and TIME data types above. Only the specified unit and larger units are used.
This shows the result of subtracting two dates, in which the second is two years later than the first:
SELECT TIMEDIFF(YEAR, '2017-01-01', '2019-01-01') AS Years; +-------+ | YEARS | |-------| | 2 | +-------+
This shows that the value is truncated rather than rounded. The difference is closer to 12 months than to 11, but Snowflake calculates the difference as 11 months:
SELECT TIMEDIFF(MONTH, '2017-01-1', '2017-12-31') AS Months; +--------+ | MONTHS | |--------| | 11 | +--------+
There are additional examples in DATEDIFF.