DATEDIFF

Returns the time span between two dates, in the intervals specified. DATEDIFF excludes the start date in its calculation.

Behavior Type

  • Immutable if start and end dates are TIMESTAMP , DATE, TIME, or INTERVAL
  • Stable if start and end dates are TIMESTAMPTZ

Syntax

DATEDIFF ( datepart, start, end );

Parameters

datepart

Specifies the type of date or time intervals that DATEDIFF returns. If datepart is an expression, it must be enclosed in parentheses:

DATEDIFF((expression), start, end;

datepart must evaluate to one of the following string literals, either quoted or unquoted:

  • year | yy | yyyy
  • quarter | qq | q
  • month | mm | m
  • day | dayofyear | dd | d | dy | y
  • week | wk | ww
  • hour | hh
  • minute | mi | n
  • second | ss | s
  • millisecond | ms
  • microsecond | mcs | us
start, end

Specify the start and end dates, where start and end evaluate to one of the following data types:

If end < start, DATEDIFF returns a negative value.

TIME and INTERVAL data types are invalid for start and end dates if datepart is set to year, quarter, or month.

Compatible Start and End Date Data Types

The following table shows which data types can be matched as start and end dates:

  DATE TIMESTAMP TIMESTAMPTZ TIME INTERVAL
DATE • • •    
TIMESTAMP • • •    
TIMESTAMPTZ • • •    
TIME       •  
INTERVAL         •

For example, if you set the start date to an INTERVAL data type, the end date must also be an INTERVAL, otherwise Vertica returns an error:

 SELECT DATEDIFF(day, INTERVAL '26 days', INTERVAL '1 month ');
 datediff
----------
        4
(1 row)

Date Part Intervals

DATEDIFF uses the datepart argument to calculate the number of intervals between two dates, rather than the actual amount of time between them. DATEDIFF uses the following cutoff points to calculate those intervals:

  • year: January 1
  • quarter: January 1, April 1, July 1, October 1
  • month: the first day of the month
  • week: Sunday at midnight (24:00)

For example, if datepart is set to year, DATEDIFF uses January 01 to calculate the number of years between two dates. The following DATEDIFF statement sets datepart to year, and specifies a time span 01/01/2005 - 06/15/2008:

SELECT DATEDIFF(year, '01-01-2005'::date, '12-31-2008'::date);
 datediff 
----------
        3
(1 row)

DATEDIFF always excludes the start date when it calculates intervals—in this case, 01/01//2005. DATEDIFF considers only calendar year starts in its calculation, so in this case it only counts years 2006, 2007, and 2008. The function returns 3, although the actual time span is nearly four years.

If you change the start and end dates to 12/31/2004 and 01/01/2009, respectively, DATEDIFF also counts years 2005 and 2009. This time, it returns 5, although the actual time span is just over four years:

=> SELECT DATEDIFF(year, '12-31-2004'::date, '01-01-2009'::date);
 datediff
----------
        5
(1 row)

Similarly, DATEDIFF uses month start dates when it calculates the number of months between two dates. Thus, given the following statement, DATEDIFF counts months February through September and returns 8:

=> SELECT DATEDIFF(month, '01-31-2005'::date, '09-30-2005'::date);
 datediff
----------
        8
(1 row)

See Also

TIMESTAMPDIFF