8.8. Date/Time Functions and Operators

Table 8-25 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 8-24 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.

Table 8-24. Date/Time Operators

OperatorExampleResult
+ 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-25. Date/Time Functions

FunctionReturn TypeDescriptionExampleResult
current_dateDATEToday's date;   
current_timestampTIMESTAMPDate and time;   
date_part(TEXT, TIMESTAMP)DOUBLE PRECISIONGet subfield (equivalent to extract); see Section 8.8.1 date_part('hour', timestamp '2001-02-16 20:38:40')20
date_trunc(TEXT, TIMESTAMP)TIMESTAMPTruncate 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 PRECISIONGet subfield; see Section 8.8.1 extract(hour from timestamp '2001-02-16 20:38:40')20
trunc(DATE)DATETruncate to specified format mask; trunc(sysdate,'mm')2005-05-01 00:00:00+05
isfinite(TIMESTAMP)BOOLEANTest for finite time stamp (not equal to infinity)isfinite(timestamp '2001-02-16 21:28:30')true
localtimestampTIMESTAMPDate and time;   
now()TIMESTAMPCurrent 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') 
sysdateDATECurrent date and time;   
timeofday()TEXTCurrent date and time;   

8.8.1. EXTRACT, date_part

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[1])

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

8.8.2. TRUNC

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 DATE. Please refer to Table 8-26 for the permitted format models to use in fmt.

Table 8-26. TRUNC format masks

ExpressionReturn TypeDescription
timestamp without time zone AT TIME ZONE zone timestamp with time zoneConvert local time in given time zone to UTC
timestamp with time zone AT TIME ZONE zone timestamp without time zoneConvert UTC to local time in given time zone
time with time zone AT TIME ZONE zone time with time zoneConvert 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.

8.8.3. date_trunc

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

8.8.4. Current Date/Time

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.

Notes

[1]

60 if leap seconds are implemented by the operating system