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' |
age(ts,ts) |
Returns the interval between two timestamps. The returned value
is of the same precision as the most precise argument. For example:
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:
|
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:
|
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:
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. |
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) |
First argument data type | Second argument data type | Returns |
---|---|---|
date | date | numeric(8,0) |
numeric(8,0) | date | |
time | time | numeric(6,0) |
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) |