Skip to main content

TIMESTAMPDIFF function

Returns the span between two timestamps.

Syntax

TIMESTAMPDIFF(datepart, start, end)

Arguments

  • datepart: one of YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, or MICROSECOND.

  • start: a TIMESTAMP, TIMESTAMP_TZ, or DATE.

  • end: a TIMESTAMP, TIMESTAMP_TZ, or DATE.

Returns

A BIGINT. In Vertica, the ceiling of the absolute value will be used when rounding the difference. In Databricks, the floor will be used.

Examples

> SELECT TIMESTAMPDIFF(HOUR, TIMESTAMP '2001-02-03 04:00:00', TIMESTAMP '2001-02-03 05:00:00');
 1
> SELECT TIMESTAMPDIFF(HOUR, TIMESTAMP '2001-02-03 04:30:00', TIMESTAMP '2001-02-03 06:00:00');
 2 (Vertica)
 1 (Databricks)
> SELECT TIMESTAMPDIFF(HOUR, DATE '2001-02-03', DATE '2001-02-03');
 0