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. The calculation logic for rounding the difference varies by environment:

  • Vertica: The ceiling of the absolute value is used when rounding the difference.

  • Spark-based engine: The floor of the absolute value is used when rounding the difference.

Examples

-- Standard calculation supported across all environments
SELECT TIMESTAMPDIFF(HOUR, TIMESTAMP '2001-02-03 04:00:00', TIMESTAMP '2001-02-03 05:00:00');
-- Result: 1

-- Example showing logic differences in calculation:
SELECT TIMESTAMPDIFF(HOUR, TIMESTAMP '2001-02-03 04:30:00', TIMESTAMP '2001-02-03 06:00:00');
-- Result: 2 (Vertica)
-- Result: 1 (Spark-based engine)

-- Standard calculation using DATE types
SELECT TIMESTAMPDIFF(HOUR, DATE '2001-02-03', DATE '2001-02-03');
-- Result: 0