Date/time functions

The following table describes the date/time functions.

For syntax, see Table 1.
For interval data types, see Netezza SQL interval support.
For data/time units, see Extract date and time values.

Table 1. Date/time functions
Type	Description
`add_months(date,n)`	Returns the date that corresponds to `date` plus the number of months indicated by the integer `n`. If the resulting date would have more days than are available in the resulting month, the result is the last day of that month. Otherwise, the resulting date has the same day component as `date`. For example: `add_months('March 03, 2015',2)` returns `'2015-05-03 00:00:00'` `add_months('March 31, 2015',-1)` returns `'2015-02-28 00:00:00'`
`age(ts,ts)` `age(ts)`	Returns the interval between two timestamps. The returned value is of the same precision as the most precise argument. For example: `select age('10-22-2003', '7-6-2002')` returns `'1 year 3 mons 16 days'` `select age('10-22-2003 09:46:07.325', '7-6-2002')` returns `'1 year 3 mons 16 days 09:46:07.325'` If you specify a single argument, the function returns the interval between the current time and the specified timestamp. This is equivalent to `age(CURRENT_TIMESTAMP, ts)`. IBM® Netezza® SQL interval support is nonstandard.
`date_part(units,col)`	Extracts the subfield represented by `units` from the date/time value, interval, or duration specified for `col`. This function is equivalent to `extract()`.
`date_trunc(units,date)`	Truncates the date specified for `date` to the precision specified by `units`.
`extract(units FROM col)`	Extracts the subfield represented by `units` from the date/time value, interval, or duration specified for `col`. This function is equivalent to `date_part()`.
`last_day(date)`	Returns the last day of the month of the specified date.
`months_between(d1,d2)`	Returns the number of months between dates `d1` and `d2`: If `d1` is later than `d2`, the result is positive. If `d1` is earlier than `d2`, the result is negative. If `d1` and `d2` are either the same days of the month or both the last days of their respective months, the result is an integer. Otherwise, the result includes a fractional portion based on a 31-day month. For example: `select months_between('2015-05-23', '2015-04-23')` returns `1` `select months_between('2015-02-28', '2015-05-31')` returns `-3` `select months_between('2015-05-15', '2014-05-05')` returns `12.322580645` (12 complete months, plus 10 additional days; 10/31=0.322580645.)
`next_day(date,weekday)`	Returns the date of the weekday that follows a particular date. The `date` argument can be a date or timestamp. The format of the returned value is the same as the format of the `date` argument. The `weekday` argument is a string literal of three or more characters that indicates the day of the week ('SUN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT') for which the date is to be returned. It must be enclosed in single quotation marks and can contain either uppercase or lowercase letters. Any characters that are specified after the third character are ignored. For example, the first Saturday after Wednesday, 25 December 2013 is on 28 December 2013: `select next_day('12-25-2013','Saturday')` returns `'2013-12-28'`
`now()`	This function is the same as current_timestamp. For more information, see Conversion functions.
`overlaps(a,b,c,d)`	Determines whether two time intervals overlap. The data types of: Parameters a and c can be either TIME or TIMESTAMP, but must the same for both Parameters b and d can be one of TIME, TIME WITH TIME ZONE, or INTERVAL, but must the same for both Netezza SQL supports the standard SQL overlaps predicate.
`duration_add(a,b)`	Returns the duration that results when the two arguments (represented by `a` and `b`) are added. Valid combinations of argument data types are described in Table 2.
`duration_subtract(a,b)`	Returns the duration that results when the second argument (`b`) is subtracted from the first argument (`a`). Valid combinations of argument data types are described in Table 3.
`timeofday()`	The function is the verbose string version of current_timestamp. For example, `Mon 01 Dec 2014 16:12:05 EST`. The format of the output is determined by the conversion function that is used (see Conversion functions)
`timezone(timestamp from_tz to_tz)`	Converts the input timestamp value in the `from_tz` timezone to a timestamp in the `to_tz` timezone value. For example: `select TIMEZONE(timestamp '2000-07-04 17:00:00', 'America/New_York', 'America/Los_Angeles'); TIMEZONE --------------------- 2000-07-04 14:00:00 (1 row)` The system uses the ICU library to verify and resolve the supplied timezone values. See http://userguide.icu-project.org/datetime/timezone for more information about programmatic IDs like `Canada/Newfoundland` or raw offset values like `GMT-03:30` to specify timezones reliably. If the function cannot resolve the timezones specified in the query, the query fails with the message `ERROR: Time zone 'invalid_tz' not recognized`.

Table 2. Valid combinations of data types for the arguments of the duration_add function
First argument data type	Second argument data type	Returns
date	numeric(8,0)	date
time	numeric(6,0)	time
timestamp	numeric(6,0)	timestamp
	numeric(8,0)
	numeric(14,0)
	numeric(15,1)
	numeric(16,2)
	numeric(17,3)
	numeric(18,4)
	numeric(19,5)
	numeric(20,6)

Table 3. Valid combinations of data types for the arguments of the duration_subtract function
First argument data type	Second argument data type	Returns
date	date	numeric(8,0)
date	numeric(8,0)	date
time	time	numeric(6,0)
time	numeric(6,0)	time
timestamp	timestamp	numeric(20,6)
	numeric(6,0)	timestamp
	numeric(8,0)
	numeric(14,0)
	numeric(15,1)
	numeric(16,2)
	numeric(17,3)
	numeric(18,4)
	numeric(19,5)
	numeric(20,6)

For the second argument or the returned value of the duration_add or duration_subtract function:

The type numeric(6,0) represents a duration of the form hhmmss, for example 030745 for three hours, seven minutes, and 45 seconds.
The type numeric(8,0) represents a duration of the form YYYYMMDD, for example 00020525 for two years, five months, and 25 days.
Other numeric values contain 14-20 digits:
- The first 8 digits indicate the date portion of the duration in YYYYMMDD format.
- The next 6 digits indicate the time portion of the duration in hhmmss format.
- The next 0-6 digits indicate a fraction of a second.
For example, the duration 00010521010521999::numeric(17,3) represents a time period of one year, five months, 21 days, one hour, five minutes, and 21.999 seconds.