datediff
(timestamp) function
Applies to: Databricks SQL Databricks Runtime 10.4 LTS and above
Returns the difference between two timestamps measured in unit
s. This function is a synonym for timestampdiff function.
Syntax
datediff(unit, start, end)
unit
{ MICROSECOND |
MILLISECOND |
SECOND |
MINUTE |
HOUR |
DAY |
WEEK |
MONTH |
QUARTER |
YEAR }
Arguments
unit
: A unit of measure.start
: A startingTIMESTAMP
expression.end
: An endingTIMESTAMP
expression.
Returns
A BIGINT
.
If start
is greater than end
the result is negative.
The function counts whole elapsed units based on UTC
with a DAY
being 86400 seconds.
One month is considered elapsed when the calendar month has increased and the calendar day and time is equal or greater to the start. Weeks, quarters, and years follow from that.
Examples
-- One second shy of a month elapsed
> SELECT datediff(MONTH, TIMESTAMP'2021-02-28 12:00:00', TIMESTAMP'2021-03-28 11:59:59');
0
-- One month has passed even though its' not end of the month yet because day and time line up.
> SELECT datediff(MONTH, TIMESTAMP'2021-02-28 12:00:00', TIMESTAMP'2021-03-28 12:00:00');
1
-- Start is greater than the end
> SELECT datediff(YEAR, DATE'2021-01-01', DATE'1900-03-28');
-120