Table 8-26 shows the available
functions for date/time value processing, with details appearing in
the following subsections. Table 8-25 illustrates the behaviors of
the basic arithmetic operators (+,
*, etc.). For formatting functions, refer to
Section 8.7. You should be familiar with
the background information on date/time data types from Section 7.4.
A Redwood compatible ROUND function is also available to round a date value
to the nearest date according to a specified format mask. The syntax
for ROUND function is as follows:
ROUND(input_date IN DATE [, format_mask VARCHAR2])
The function returns the rounded DATE. The format mask is optional, if omitted,
date is rounded to the nearest day. The time component of a date always gets rounded to midnight.
Note: For the to_date, round and trunk functions, the valid date range is -4712 - +9999 (in years and excluding 0).
Any operation on these functions not within this range will raise an error.
Table 8-25. Date/Time Operators
Operator | Example | Result |
---|
+ | date '2001-09-28' + integer '7' | date '2001-10-05' |
+ | date '2001-09-28' + time '03:00' | timestamp '2001-09-28 03:00' |
- | date '2001-10-01' - date '2001-09-28' | integer '3' |
- | date '2001-10-01' - integer '7' | date '2001-09-24' |
Table 8-26. Date/Time Functions
Function | Return Type | Description | Example | Result |
---|
current_date | DATE | Today's date;
| | |
current_timestamp | TIMESTAMP | Date and time;
| | |
date_part (TEXT, TIMESTAMP) | DOUBLE PRECISION | Get subfield (equivalent to
extract ); see Section 8.8.1
| date_part('hour', timestamp '2001-02-16 20:38:40') | 20 |
date_trunc (TEXT, TIMESTAMP) | TIMESTAMP | Truncate to specified precision; see also Section 8.8.3
| date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00 |
extract (field from
TIMESTAMP) | DOUBLE PRECISION | Get subfield; see Section 8.8.1
| extract(hour from timestamp '2001-02-16 20:38:40') | 20 |
trunc (DATE) | TIMESTAMP | Truncate to specified format mask; TIMESTAMP is used to allow compatibility with Redwood dates.
| trunc(sysdate,'mm') | 2005-05-01 00:00:00+05 |
isfinite (TIMESTAMP) | BOOLEAN | Test for finite time stamp (not equal to infinity) | isfinite(timestamp '2001-02-16 21:28:30') | true |
localtimestamp | TIMESTAMP | Date and time;
| | |
now () | TIMESTAMP | Current date and time (equivalent to
current_timestamp );
| | |
nvl (expr1,
expr2) | (same as argument types; where both arguments are of the same datatype) | if expr1 is null, then nvl returnsexpr2
| nvl(hiredate,'20-DEC-80') | |
round() | DATE | The function returns the rounded
DATE. The format mask is
optional, if omitted, date is rounded to the nearest day.
Table 8-21 shows the supported format masks for the round function.
| round ( to_date ( '01- MAR 1994' ) ) | 01-MAR-94 00:00:00 |
sysdate | TIMESTAMP | Current date and time;
| | |
timeofday() | TEXT | Current date and time;
| | |
EXTRACT (field FROM source)
The extract
function retrieves subfields
from date/time values, such as year or hour.
source is a value expression that
evaluates to type TIMESTAMP or interval.
(Expressions of type DATE or time will
be cast to TIMESTAMP and can therefore be used as
well.) field is an identifier or
string that selects what field to extract from the source value.
The extract
function returns values of type
DOUBLE PRECISION.
The following are valid field names:
- century
The century
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13') FROM DUAL;
Result: 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 21
The first century starts at 0001-01-01 00:00:00 AD, although
they did not know it at the time. 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)
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 16
- decade
The year field divided by 10
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 200
- dow
The day of the week (0 - 6; Sunday is 0) (for
TIMESTAMP values only)
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 5
- doy
The day of the year (1 - 365/366) (for TIMESTAMP values only)
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 47
- hour
The hour field (0 - 23)
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 20
- microseconds
The seconds field, including fractional parts, multiplied by 1
000 000. Note that this includes full seconds.
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5') FROM DUAL;
Result: 28500000
- millennium
The millennium
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 3
Years in the 1900s are in the second millennium.
The third millennium starts January 1, 2001.
- milliseconds
The seconds field, including fractional parts, multiplied by
1000. Note that this includes full seconds.
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5') FROM DUAL;
Result: 28500
- minute
The minutes field (0 - 59)
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 38
- 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)
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 2
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months') FROM DUAL;
Result: 3
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months') FROM DUAL;
Result: 1
- quarter
The quarter of the year (1 - 4) that the day is in (for
TIMESTAMP values only)
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 1
- second
The seconds field, including fractional parts (0 -
59)
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 40
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5') FROM DUAL;
Result: 28.5
- week
The number of the week of the year that the day is in. By definition
(ISO 8601), the first week of a year
contains January 4 of that year. (The ISO-8601
week starts on Monday.) In other words, the first Thursday of
a year is in week 1 of that year. (for TIMESTAMP values only)
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 7
- year
The year field. Keep in mind there is no 0 AD, so subtracting
BC years from AD years should be done with care.
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 2001
The extract
function is primarily intended
for computational processing. For formatting date/time values for
display, see Section 8.7.
The date_part
function is modeled on the traditional
Ingres equivalent to the
SQL-standard function extract
:
date_part('field', source)
Note that here the field parameter needs to
be a string value, not a name. The valid field names for
date_part
are the same as for
extract
.
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes') FROM DUAL;
Result: 4
The TRUNC function returns date with
the time portion of the day truncated to the unit specified by the format
model fmt. The value of the returned datatype is TIMESTAMP.
Please refer to Table 8-27 for the permitted format models to
use in fmt.
Table 8-27. TRUNC format masks
Expression | Return Type | Description |
---|
timestamp without time zone AT TIME ZONE zone
| timestamp with time zone | Convert local time in given time zone to UTC |
timestamp with time zone AT TIME ZONE zone
| timestamp without time zone | Convert UTC to local time in given time zone |
time with time zone AT TIME ZONE zone
| time with time zone | Convert local time across time zones |
In these expressions, the desired time zone zone can be
specified either as a text string (e.g., 'PST')
or as an interval (e.g., INTERVAL '-08:00').
Examples (supposing that the local time zone is PST8PDT):
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
Result: 2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40
The first example takes a zone-less time stamp and interprets it as MST time
(UTC-7) to produce a UTC time stamp, which is then rotated to PST (UTC-8)
for display. The second example takes a time stamp specified in EST
(UTC-5) and converts it to local time in MST (UTC-7).
The function timezone
(zone,
timestamp) is equivalent to the SQL-conforming construct
timestamp AT TIME ZONE
zone.
The function date_trunc
is conceptually
similar to the trunc
function for numbers.
date_trunc('field', source)
source is a value expression of type
TIMESTAMP or interval.
(Values of type DATE and
time are cast automatically, to TIMESTAMP or
interval respectively.)
field selects to which precision to
truncate the input value. The return value is of type
TIMESTAMP or interval
with all fields that are less significant than the
selected one set to zero (or one, for day and month).
Valid values for field are:
microseconds |
milliseconds |
second |
minute |
hour |
day |
week |
month |
year |
decade |
century |
millennium |
Examples:
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Result: 2001-01-01 00:00:00
The following functions are available to obtain the current date and/or
time:
CURRENT_DATE
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP ( precision )
LOCALTIMESTAMP
LOCALTIMESTAMP ( precision )
SYSDATE
CURRENT_TIMESTAMP
,
LOCALTIMESTAMP
can optionally be given
a precision parameter, which causes the result to be rounded
to that many fractional digits in the seconds field. Without a precision parameter,
the result is given to the full available precision.
Some examples:
SELECT CURRENT_DATE FROM DUAL;
Result: 2001-12-23
SELECT CURRENT_TIMESTAMP FROM DUAL;
Result: 2001-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2) FROM DUAL;
Result: 2001-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP FROM DUAL;
Result: 2001-12-23 14:39:53.662522
SELECT SYSDATE FROM DUAL;
Result: 2001-12-23 14:39:53
The function now()
is the traditional
EnterpriseDB equivalent to
CURRENT_TIMESTAMP
.
There is also the function timeofday()
, which for historical
reasons returns a TEXT string rather than a TIMESTAMP value:
SELECT timeofday() FROM DUAL;
Result: Sat Feb 17 19:07:32.000126 2001 EST
It is important to know that
CURRENT_TIMESTAMP
and related functions return
the start time of the current transaction; their values do not
change during the transaction. This is considered a feature:
the intent is to allow a single transaction to have a consistent
notion of the "current" time, so that multiple
modifications within the same transaction bear the same
time stamp. timeofday()
returns the wall-clock time and does advance during transactions.
Note: Other database systems may advance these values more
frequently.