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.
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:
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 :
|
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 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 |
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) |
- 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.
00010521010521999::numeric(17,3)
represents a time period of one year, five months, 21 days, one hour, five minutes, and 21.999 seconds.