Date and time functions perform conversion, extraction, or manipulation operations on date and time data types and can return date and time information.
Date and time functions perform conversion, extraction, or manipulation operations on date and time data types and can return date and time information.
Usage
Functions that take TIME or TIMESTAMP inputs come in two variants:
TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE
TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE
For brevity, these variants are not shown separately.
The + and * operators come in commutative pairs; for example, both DATE + INTEGER and INTEGER + DATE. We show only one of each such pair.
Daylight savings time considerations
When adding an INTERVAL value to (or subtracting an INTERVAL value from) a TIMESTAMP WITH TIME ZONE value, the days component advances (or decrements) the date of the TIMESTAMP WITH TIME ZONE by the indicated number of days. Across daylight saving time changes (with the session time zone set to a time zone that recognizes DST), this means INTERVAL '1 day' does not necessarily equal INTERVAL '24 hours'.
For example, with the session time zone set to CST7CDT:
TIMESTAMP WITH TIME ZONE '2014-04-02 12:00-07' + INTERVAL '1 day'
produces
TIMESTAMP WITH TIME ZONE '2014-04-03 12:00-06'
Adding INTERVAL '24 hours' to the same initial TIMESTAMP WITH TIME ZONE produces
TIMESTAMP WITH TIME ZONE '2014-04-03 13:00-06',
This result occurs because there is a change in daylight saving time at 2014-04-03 02:00 in time zone CST7CDT.
Date/time functions in transactions
Certain date/time functions such as
CURRENT_TIMESTAMP and
NOW return the start time of the current transaction; for the duration of that transaction, they return the same value. Other date/time functions such as
TIMEOFDAY always return the current time.
Adds the specified number of months to a date and returns the sum as a DATE.
Adds the specified number of months to a date and returns the sum as a DATE. In general, ADD_MONTHS returns a date with the same day component as the start date. For example:
If the start date's day component is the last day of that month, and the result month has more days than the start date month, ADD_MONTHS returns the last day of the result month. For example:
The following example sets the timezone to EST; it then adds 24 months to a TIMESTAMPTZ that specifies a PST time zone, so ADD_MONTHS takes into account the time change:
=> SET TIME ZONE 'America/New_York';
SET
VMart=> SELECT ADD_MONTHS('2008-02-29 23:30 PST'::TIMESTAMPTZ, 24);
ADD_MONTHS
------------
2010-03-01
(1 row)
2 - AGE_IN_MONTHS
Returns the difference in months between two dates, expressed as an integer.
Returns the difference in months between two dates, expressed as an integer.
Behavior type
Immutable if both date arguments are of data type TIMESTAMP
Stable if either date is a TIMESTAMPTZ or only one argument is supplied
Syntax
AGE_IN_MONTHS ( [ date1,] date2 )
Parameters
date1 date2
Specify the boundaries of the period to measure. If you supply only one argument, Vertica sets date2 to the current date. Both parameters must evaluate to one of the following data types:
DATE
TIMESTAMP
TIMESTAMPTZ
If date1 < date2, AGE_IN_MONTHS returns a negative value.
Examples
Get the age in months of someone born March 2 1972, as of June 21 1990:
Returns the difference in years between two dates, expressed as an integer.
Returns the difference in years between two dates, expressed as an integer.
Behavior type
Immutable if both date arguments are of data type TIMESTAMP
Stable if either date is a TIMESTAMPTZ or only one argument is supplied
Syntax
AGE_IN_YEARS( [ date1,] date2 )
Parameters
date1 date2
Specify the boundaries of the period to measure. If you supply only one argument, Vertica sets date1 to the current date. Both parameters must evaluate to one of the following data types:
DATE
TIMESTAMP
TIMESTAMPTZ
If date1 < date2, AGE_IN_YEARS returns a negative value.
Examples
Get the age of someone born March 2 1972, as of June 21 1990:
Returns a value of type TIMESTAMP WITH TIMEZONE that represents the current system-clock time.
Returns a value of type TIMESTAMP WITH TIMEZONE that represents the current system-clock time.
CLOCK_TIMESTAMP uses the date and time supplied by the operating system on the server to which you are connected, which should be the same across all servers. The value changes each time you call it.
If you specify a column label without precision, you must also omit parentheses.
Parameters
precision
An integer value between 0-6, specifies to round the seconds fraction field result to the specified number of digits.
Examples
=> SELECT CURRENT_TIME(1) AS Time;
Time
---------------
06:51:45.2-07
(1 row)
=> SELECT CURRENT_TIME(5) AS Time;
Time
-------------------
06:51:45.18435-07
(1 row)
7 - CURRENT_TIMESTAMP
Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction.
Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction.
The return value does not change during the transaction. Thus, multiple calls to CURRENT_TIMESTAMP within the same transaction return the same timestamp.
The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from –1 to 1.
DAY
The day (of the month) field (1–31).
DECADE
The year field divided by 10.
DOQ
The day within the current quarter. DOQ recognizes leap year days.
DOW
Zero-based day of the week, where Sunday=0.
Note
EXTRACT's day of week numbering differs from the function
TO_CHAR .
DOY
The day of the year (1–365/366)
EPOCH
Specifies to return one of the following:
For DATE and TIMESTAMP values: the number of seconds before or since 1970-01-01 00:00:00-00 (if before, a negative number).
For INTERVAL values, the total number of seconds in the interval.
HOUR
The hour field (0–23).
ISODOW
The ISO day of the week, an integer between 1 and 7 where Monday is 1.
ISOWEEK
The ISO week of the year, an integer between 1 and 53.
ISOYEAR
The ISO year.
MICROSECONDS
The seconds field, including fractional parts, multiplied by 1,000,000. This includes full seconds.
MILLENNIUM
The millennium number, where the first millennium is 1 and each millenium starts on 01-01-y001. For example, millennium 2 starts on 01-01-1001.
MILLISECONDS
The seconds field, including fractional parts, multiplied by 1000. This includes full seconds.
MINUTE
The minutes field (0 - 59).
MONTH
For TIMESTAMP values, the number of the month within the year (1 - 12) ; for interval values the number of months, modulo 12 (0 - 11).
QUARTER
The calendar quarter of the specified date as an integer, where the January-March quarter is 1, valid only for TIMESTAMP values.
SECOND
The seconds field, including fractional parts, 0–59, or 0-60 if the operating system implements leap seconds.
TIME ZONE
The time zone offset from UTC, in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.
TIMEZONE_HOUR
The hour component of the time zone offset.
TIMEZONE_MINUTE
The minute component of the time zone offset.
WEEK
The number of the week of the calendar year that the day is in.
YEAR
The year field. There is no 0 AD, so subtract BC years from AD years accordingly.
Notes
According to the ISO-8601 standard, the week starts on Monday, and the first week of a year contains January 4. Thus, an early January date can sometimes be in the week 52 or 53 of the previous calendar year. For example:
Truncates date and time values to the specified precision.
Truncates date and time values to the specified precision. The return value is the same data type as the input value. All fields that are less than the specified precision are set to 0, or to 1 for day and month.
A string constant that specifies precision for the truncated value. See Precision Field Values below. The precision must be valid for the trunc-target date or time.
trunc-target
Valid date/time expression.
Precision field values
MILLENNIUM
The millennium number.
CENTURY
The century number.
The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries.
DECADE
The year field divided by 10.
YEAR
The year field. Keep in mind there is no 0 AD, so subtract BC years from AD years with care.
QUARTER
The calendar quarter of the specified date as an integer, where the January-March quarter is 1.
MONTH
For timestamp values, the number of the month within the year (1–12) ; for interval values the number of months, modulo 12 (0–11).
WEEK
The number of the week of the year that the day is in.
According to the ISO-8601 standard, the week starts on Monday, and the first week of a year contains January 4. Thus, an early January date can sometimes be in the week 52 or 53 of the previous calendar year. For example:
The following example returns the year from the input timestamptz '2012-02-24 13:38:40'. The function also defaults the month and day to January 1, truncates the hour:minute:second of the timestamp, and appends the time zone (-05):
=> SELECT DATE_TRUNC('YEAR', TIMESTAMPTZ '2012-02-24 13:38:40') AS YEAR;
YEAR
------------------------
2012-01-01 00:00:00-05
(1 row)
The following example returns the year and month and defaults day of month to 1, truncating the rest of the string:
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:
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:
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 DAYS (DATE '2011-01-22');
DAYS
--------
734159
(1 row)
=> SELECT DAYS (DATE 'March 15, 0044 BC');
DAYS
--------
-15997
(1 row)
18 - EXTRACT
Retrieves sub-fields such as year or hour from date/time values and returns values of type NUMERIC.
Retrieves sub-fields such as year or hour from date/time values and returns values of type
NUMERIC. EXTRACT is intended for computational processing, rather than for formatting date/time values for display.
Behavior type
Immutable if the specified date is a TIMESTAMP, DATE, or INTERVAL
The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from –1 to 1.
DAY
The day (of the month) field (1–31).
DECADE
The year field divided by 10.
DOQ
The day within the current quarter. DOQ recognizes leap year days.
DOW
Zero-based day of the week, where Sunday=0.
Note
EXTRACT's day of week numbering differs from the function
TO_CHAR .
DOY
The day of the year (1–365/366)
EPOCH
Specifies to return one of the following:
For DATE and TIMESTAMP values: the number of seconds before or since 1970-01-01 00:00:00-00 (if before, a negative number).
For INTERVAL values, the total number of seconds in the interval.
HOUR
The hour field (0–23).
ISODOW
The ISO day of the week, an integer between 1 and 7 where Monday is 1.
ISOWEEK
The ISO week of the year, an integer between 1 and 53.
ISOYEAR
The ISO year.
MICROSECONDS
The seconds field, including fractional parts, multiplied by 1,000,000. This includes full seconds.
MILLENNIUM
The millennium number, where the first millennium is 1 and each millenium starts on 01-01-y001. For example, millennium 2 starts on 01-01-1001.
MILLISECONDS
The seconds field, including fractional parts, multiplied by 1000. This includes full seconds.
MINUTE
The minutes field (0 - 59).
MONTH
For TIMESTAMP values, the number of the month within the year (1 - 12) ; for interval values the number of months, modulo 12 (0 - 11).
QUARTER
The calendar quarter of the specified date as an integer, where the January-March quarter is 1, valid only for TIMESTAMP values.
SECOND
The seconds field, including fractional parts, 0–59, or 0-60 if the operating system implements leap seconds.
TIME ZONE
The time zone offset from UTC, in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.
TIMEZONE_HOUR
The hour component of the time zone offset.
TIMEZONE_MINUTE
The minute component of the time zone offset.
WEEK
The number of the week of the calendar year that the day is in.
YEAR
The year field. There is no 0 AD, so subtract BC years from AD years accordingly.
Examples
Extract the day of the week and day in quarter from the current TIMESTAMP:
=> SELECT CURRENT_TIMESTAMP AS NOW;
NOW
-------------------------------
2016-05-03 11:36:08.829004-04
(1 row)
=> SELECT EXTRACT (DAY FROM CURRENT_TIMESTAMP);
date_part
-----------
3
(1 row)
=> SELECT EXTRACT (DOQ FROM CURRENT_TIMESTAMP);
date_part
-----------
33
(1 row)
Returns the current statement's start date and time as a TIMESTAMP value.
Returns the current statement's start date and time as a TIMESTAMP value. This function is identical to
SYSDATE.
GETDATE uses the date and time supplied by the operating system on the server to which you are connected, which is the same across all servers. Internally, GETDATE converts
STATEMENT_TIMESTAMP from TIMESTAMPTZ to TIMESTAMP.
Returns the current statement's start date and time as a TIMESTAMP value.
Returns the current statement's start date and time as a TIMESTAMP value.
GETUTCDATE uses the date and time supplied by the operating system on the server to which you are connected, which is the same across all servers. Internally, GETUTCDATE converts
STATEMENT_TIMESTAMP at TIME ZONE 'UTC'.
SELECT ISFINITE(TIMESTAMP '2009-02-16 21:28:30');
ISFINITE
----------
t
(1 row)
SELECT ISFINITE(TIMESTAMP 'INFINITY');
ISFINITE
----------
f
(1 row)
23 - JULIAN_DAY
Returns the integer value of the specified day according to the Julian calendar, where day 1 is the first day of the Julian period, January 1, 4713 BC (on the Gregorian calendar, November 24, 4714 BC).
Returns the integer value of the specified day according to the Julian calendar, where day 1 is the first day of the Julian period, January 1, 4713 BC (on the Gregorian calendar, November 24, 4714 BC).
Behavior type
Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR
SQL does not support any function that returns the first day in the month of a given date. You must use other functions to work around this limitation. For example:
Rounds the result to the specified number of fractional digits in the seconds field.
Examples
=> CREATE TABLE t1 (a int, b int);
CREATE TABLE
=> INSERT INTO t1 VALUES (1,2);
OUTPUT
--------
1
(1 row)
=> SELECT LOCALTIME time;
time
-----------------
15:03:14.595296
(1 row)
=> INSERT INTO t1 VALUES (3,4);
OUTPUT
--------
1
(1 row)
=> SELECT LOCALTIME;
time
-----------------
15:03:14.595296
(1 row)
=> COMMIT;
COMMIT
=> SELECT LOCALTIME;
time
-----------------
15:03:49.738032
(1 row)
26 - LOCALTIMESTAMP
Returns a value of type TIMESTAMP/TIMESTAMPTZ that represents the start of the current transaction, and remains unchanged until the transaction is closed.
Returns a value of type TIMESTAMP/TIMESTAMPTZ that represents the start of the current transaction, and remains unchanged until the transaction is closed. Thus, multiple calls to LOCALTIMESTAMP within a given transaction return the same timestamp.
Returns the number of months between two dates. MONTHS_BETWEEN can return an integer or a FLOAT:
Integer: The day portions of date1 and date2 are the same, and neither date is the last day of the month. MONTHS_BETWEEN also returns an integer if both dates in date1 and date2 are the last days of their respective months. For example, MONTHS_BETWEEN calculates the difference between April 30 and March 31 as 1 month.
FLOAT: The day portions of date1 and date2 are different and one or both dates are not the last day of their respective months. For example, the difference between April 2 and March 1 is 1.03225806451613. To calculate month fractions, MONTHS_BETWEEN assumes all months contain 31 days.
MONTHS_BETWEEN disregards timestamp time portions.
Behavior type
Immutable if both date arguments are of data type TIMESTAMP or DATE
The day of the week to process, a CHAR or VARCHAR string or character constant. Supply the full English name such as Tuesday, or any conventional abbreviation, such as Tue or Tues. day-string is not case sensitive and trailing spaces are ignored.
Examples
Get the date of the first Monday that follows April 29 2016:
=> SELECT NEXT_DAY('4-29-2016'::TIMESTAMP,'Monday') "NEXT DAY" ;
NEXT DAY
------------
2016-05-02
(1 row)
Get the first Tuesday that follows today:
SELECT NEXT_DAY(CURRENT_TIMESTAMP,'tues') "NEXT DAY" ;
NEXT DAY
------------
2016-05-03
(1 row)
34 - NOW [date/time]
Returns a value of type TIMESTAMP WITH TIME ZONE representing the start of the current transaction.
Returns a value of type TIMESTAMP WITH TIME ZONE representing the start of the current transaction. NOW is equivalent to
CURRENT_TIMESTAMP except that it does not accept a precision parameter.
The return value does not change during the transaction. Thus, multiple calls to CURRENT_TIMESTAMP within the same transaction return the same timestamp.
=> SELECT foo, bar FROM (SELECT STATEMENT_TIMESTAMP() AS foo)foo, (SELECT STATEMENT_TIMESTAMP() as bar)bar;
foo | bar
-------------------------------+-------------------------------
2016-12-07 14:55:51.543988-05 | 2016-12-07 14:55:51.543988-05
(1 row)
Returns the current statement's start date and time as a TIMESTAMP value.
Returns the current statement's start date and time as a TIMESTAMP value. This function is identical to
GETDATE.
SYSDATE uses the date and time supplied by the operating system on the server to which you are connected, which is the same across all servers. Internally, GETDATE converts
STATEMENT_TIMESTAMP from TIMESTAMPTZ to TIMESTAMP.
Aggregates data by different fixed-time intervals and returns a rounded-up input TIMESTAMP value to a value that corresponds with the start or end of the time slice interval.
Aggregates data by different fixed-time intervals and returns a rounded-up input TIMESTAMP value to a value that corresponds with the start or end of the time slice interval.
Given an input TIMESTAMP value such as 2000-10-28 00:00:01, the start time of a 3-second time slice interval is 2000-10-28 00:00:00, and the end time of the same time slice is 2000-10-28 00:00:03.
The next example uses a 3-second interval with an input value of '00:00:01'. To focus specifically on seconds, the example omits date, though all values are implied as being part of the timestamp with a given input of '00:00:01':
'00:00:00' is the start of the 3-second time slice
'00:00:03' is the end of the 3-second time slice.
'00:00:03' is also the start of the second 3-second time slice. In time slice boundaries, the end value of a time slice does not belong to that time slice; it starts the next one.
When the time slice interval is not a factor of 60 seconds, such as a given slice length of 9 in the following example, the slice does not always start or end on 00 seconds:
This is expected behavior, as the following properties are true for all time slices:
Equal in length
Consecutive (no gaps between them)
Non-overlapping
To force the above example ('2009-02-14 20:13:01') to start at '2009-02-14 20:13:00', adjust the output timestamp values so that the remainder of 54 counts up to 60:
=> SELECT TIME_SLICE('2009-02-14 20:13:01', 9 )+'6 seconds'::INTERVAL AS time;
time
---------------------
2009-02-14 20:13:00
(1 row)
Alternatively, you could use a different slice length, which is divisible by 60, such as 5:
You can use the SQL analytic functions
FIRST_VALUE and
LAST_VALUE to find the first/last price within each time slice group (set of rows belonging to the same time slice). This structure can be useful if you want to sample input data by choosing one row from each time slice group.
TIME_SLICE rounds the transaction time to the 3-second slice length.
The following example uses the analytic (window) OVER clause to return the last trading price (the last row ordered by TickTime) in each 3-second time slice partition:
=> SELECT DISTINCT TIME_SLICE(TickTime, 3), LAST_VALUE(price)OVER (PARTITION BY TIME_SLICE(TickTime, 3)
ORDER BY TickTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
Note
If you omit the windowing clause from an analytic clause, LAST_VALUE defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Results can seem non-intuitive, because instead of returning the value from the bottom of the current partition, the function returns the bottom of the window, which continues to change along with the current input row that is being processed. For more information, see Time series analytics and SQL analytics.
In the next example, FIRST_VALUE is evaluated once for each input record and the data is sorted by ascending values. Use SELECT DISTINCT to remove the duplicates and return only one output record per TIME_SLICE:
=> SELECT DISTINCT TIME_SLICE(TickTime, 3), FIRST_VALUE(price)OVER (PARTITION BY TIME_SLICE(TickTime, 3)
ORDER BY TickTime ASC)
FROM tick_store;
TIME_SLICE | ?column?
---------------------+----------
2009-09-21 00:00:06 | 20.00
2009-09-21 00:00:09 | 30.00
2009-09-21 00:00:00 | 10.00
(3 rows)
The information output by the above query can also return MIN, MAX, and AVG of the trading prices within each time slice.
=> SELECT DISTINCT TIME_SLICE(TickTime, 3),FIRST_VALUE(Price) OVER (PARTITION BY TIME_SLICE(TickTime, 3)
ORDER BY TickTime ASC),
MIN(price) OVER (PARTITION BY TIME_SLICE(TickTime, 3)),
MAX(price) OVER (PARTITION BY TIME_SLICE(TickTime, 3)),
AVG(price) OVER (PARTITION BY TIME_SLICE(TickTime, 3))
FROM tick_store;
Specifies the type of time intervals that TIMESTAMPADD adds to the specified start date. If datepart is an expression, it must be enclosed in parentheses:
TIMESTAMPADD((expression), interval, start;
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
count
Integer or integer expression that specifies the number of datepart intervals to add to start-date.
Returns the time span between two TIMESTAMP or TIMESTAMPTZ values, in the intervals specified.
Returns the time span between two TIMESTAMP or TIMESTAMPTZ values, in the intervals specified. TIMESTAMPDIFF excludes the start date in its calculation.
If end < start, TIMESTAMPDIFF returns a negative value.
Date part intervals
TIMESTAMPDIFF uses the datepart argument to calculate the number of intervals between two dates, rather than the actual amount of time between them. For detailed information, see
DATEDIFF.
Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction.
Returns a value of type
`TIME WITH TIMEZONE` that represents the start of the current transaction.
The return value does not change during the transaction. Thus, multiple calls to TRANSACTION_TIMESTAMP within the same transaction return the same timestamp.
TRANSACTION_TIMESTAMP is equivalent to
CURRENT_TIMESTAMP, except it does not accept a precision parameter.
=> SELECT foo, bar FROM (SELECT TRANSACTION_TIMESTAMP() AS foo)foo, (SELECT TRANSACTION_TIMESTAMP() as bar)bar;
foo | bar
-------------------------------+-------------------------------
2016-12-12 08:18:00.988528-05 | 2016-12-12 08:18:00.988528-05
(1 row)
Returns the week of the year for the specified date as an integer, where the first week starts on Monday and contains January 4.
Returns the week of the year for the specified date as an integer, where the first week starts on Monday and contains January 4. This function conforms with the ISO 8061 standard.
Syntax
WEEK_ISO ( date )
Behavior type
Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR