timediff function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 14.0 and above

Returns the difference between two timestamps measured in units. This function is a synonym for timestampdiff function.

Syntax

timediff(unit, start, end)

unit
 { MICROSECOND |
   MILLISECOND |
   SECOND |
   MINUTE |
   HOUR |
   DAY |
   WEEK |
   MONTH |
   QUARTER |
   YEAR }

Arguments

  • unit: A unit of measure.
  • start: A starting TIMESTAMP expression.
  • end: An ending TIMESTAMP 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 timediff(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 timediff(MONTH, TIMESTAMP'2021-02-28 12:00:00', TIMESTAMP'2021-03-28 12:00:00');
 1

-- Start is greater than the end
> SELECT timediff(YEAR, DATE'2021-01-01', DATE'1900-03-28');
 -120