DiffTime

DiffTime computes the difference between two dates or date times for a given time unit of measure.

Syntax

DiffTime(<input_1>, <input_2>[, <unit_of_measure>])

Where input_1 and input_2 are of one of the following data types:

instant
date_time
date
offset_date_time
zoned_date_time

The optional parameter, unit_of_measure, defaults to seconds, but can be overridden with any of the following values:

MILLIS
SECONDS
MINUTES
HOURS
DAYS
WEEKS
MONTHS
YEARS

Return Type

Long

Details

The count return value is number of whole units of measure between the dates or date times. Fractional values are not returned. Warning: Be mindful, as the computation returns whole numbers based on number of units of measure. Refer to the examples section.

Return values can be positive or negative, depending on the order of the date input parameters. The first input parameter is subtracted from the second input parameter.

Examples

Find the number of years, months, days, and hours between January 1, 2017 and December 31, 2017. Please note the YEARS, MONTHS, and DAYS counts are not 1, 12, and 365, respectively, in this example.

select DiffTime(ToDate('2017-01-01'), ToDate('2017-12-31'), 'YEARS') as years,
DiffTime(ToDate('2017-01-01'), ToDate('2017-12-31'), 'MONTHS') as months,
DiffTime(ToDate('2017-01-01'), ToDate('2017-12-31'), 'DAYS') as days,
DiffTime(ToDate('2017-01-01'), ToDate('2017-12-31'), 'HOURS') as hours
from Empty();

Find the number of years, months, days, and hours between January 1, 2017 and January 1, 2018. Please note the YEARS, MONTHS, and DAYS counts are 1, 12, and 365, respectively (negative values due to ordering of input parameter dates).

select DiffTime(ToDate('2018-01-01'), ToDate('2017-01-01'), 'YEARS') as years,
DiffTime(ToDate('2018-01-01'), ToDate('2017-01-01'), 'MONTHS') as months,
DiffTime(ToDate('2018-01-01'), ToDate('2017-01-01'), 'DAYS') as days,
DiffTime(ToDate('2018-01-01'), ToDate('2017-01-01'), 'HOURS') as hours
from Empty();

Related Functions