Date and Time Functions
Date and time functions operate on a date and time input value and return a string, numeric, or date and time value.
DATE_FORMAT
DATE_FORMAT
DATE_FORMAT(datetime, format)
TIME_FORMAT
TIME_FORMAT(datetime, format)
TIME_FORMAT is an alias for DATE_FORMAT.
Operand type: datetime is a DATE, TIME, or TIMESTAMP; format is a character string
Result type: VARCHAR
Returns datetime formatted according to the format string.
The specifiers in the following table can be used in the format string. The “%” character is required before format specifier characters. If the format specifier is inappropriate to the data type of datetime, then NULL is returned.
DATE_FORMAT('2010-10-03 22:23:00', '%W %M %Y')
returns 'Sunday October 2010'
DATE_FORMAT('2007-10-03 22:23:00', '%H:%i:%s')
returns '22:23:00'
DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')
returns '4th 00 Thu 04 10 Oct 277'
DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w')
returns '22 22 10 10:23:00 PM 22:23:00 00 6'
DATE_FORMAT('1999-01-01', '%X %V')
returns '1998 52'
DATE_GMT
DATE_GMT(datetime)
Result type: any character data type
Converts an absolute date into the Greenwich Mean Time character equivalent with the format yyyy_mm_dd hh:mm:ss GMT. If the absolute date does not include a time, the time portion of the result is returned as 00:00:00.
For example, the query:
SELECT DATE_GMT('1-1-98 10:13 PM PST')
returns:
1998_01_02 06:13:00 GMT
while the query:
SELECT DATE_GMT('1-1-1998')
returns:
1998_01_01 00:00:00 GMT
DATE_PART
DATE_PART(unit,date)
Result type: INTEGER
Returns an integer containing the specified unit component of the input date.
The following table lists valid unit parameters. A unit parameter must be specified using a quoted string (for example: 'YEAR'). The parameter is case sensitive.
Many of these units can also be derived using the EXTRACT function or using explicit, individual extract functions such as HOUR() or MILLISECOND().
The DATE_PART function is useful in set functions and in ensuring correct ordering in complex date manipulation. For example, if date_field contains the value 23-oct-2012, then:
DATE_PART('MONTH',DATE(date_field))
returns a value of 10 (representing October), and
DATE_PART('DAY',DATE(date_field))
returns a value of 23.
Months are numbered 1 to 12, starting with January. Hours are returned according to the 24-hour clock. Quarters are numbered 1 through 4.
Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. However, if you specify ISO-Week, which is ISO 8601 compliant, the week begins on Monday, but the first week is the week that has the first Thursday. The weeks are numbered 1 through 53.
Therefore, if you are using Week and the date falls before the first Monday in the current year, date_part returns 0. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and DATE_PART returns either 52 or 53.
The following table illustrates the difference between Week and ISO-Week:
DATE_TRUNC
DATE_TRUNC(unit,date)
Operand type: date can be absolute INGRESDATE, ANSIDATE, TIME, TIMESTAMP
Result type: Same as operand type
Returns a date value truncated to the specified
unit. Valid
unit parameters are described under
DATE_PART (see page
DATE_PART).
Where unit is DAY or greater, the day boundary is taken to be in the user's time zone. For example, the following query against a timestamp with time zone value:
SELECT DATE_TRUNC('DAY',TIMESTAMP '2011-02-03 16:12:13.000000000-08:00')
returns
2011-02-03 00:00:00.000000000-08:00
which retains the original time zone offset.
Use the DATE_TRUNC function to group all the dates within the same month or year, and so forth. For example:
DATE_TRUNC('MONTH',DATE('23-oct-1998 12:33'))
returns 1-oct-1998, and
DATE_TRUNC('YEAR',DATE('23-oct-1998'))
returns 1-jan-1998.
Truncation takes place in terms of calendar years and quarters (1-jan, 1-apr, 1-jun, and 1-oct).
To truncate in terms of a fiscal year, offset the calendar date by the number of months between the beginning of your fiscal year and the beginning of the next calendar year (6 mos for a fiscal year beginning July 1, or 4 mos for a fiscal year beginning September 1):
DATE_TRUNC('YEAR',date+'4 mos') - '4 mos'
Weeks start on Monday. The beginning of a week for an early January date falls into the previous year.
DAY
DAY('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the day portion of a date or timestamp.
DAY(TIMESTAMP '2006-12-15 12:30:55.1234') returns 15
DAYOFMONTH
DAYOFMONTH
DAYOFMONTH(datetime)
Operand type: DATE, TIME, or TIMESTAMP
Result type: INTEGER.
Returns the day of the month from the specified datetime value.
DAYOFMONTH(DATE '2011-02-15') returns 15
DAYOFWEEK
DAYOFWEEK
DAYOFWEEK(datetime [,n])
Operand type: datetime is a DATE, TIME, or TIMESTAMP; n is an integer.
Result type: INTEGER
Returns the day of the week from the specified datetime value, where 1 = Sunday unless n is used to shift the start of week.
DAYOFWEEK(DATE '2011-02-15',4) returns 6
DAYOFWEEK(DATE '2011-02-15') returns 3
DAYOFYEAR
DAYOFYEAR
DAYOFYEAR(datetime)
Returns the ordinal number of day in the year, in the range 1 to 366, for datetime.
Operand type: DATE, TIME, or TIMESTAMP
Result type: INTEGER
DAYOFYEAR(DATE '2011-02-04') returns 35
DOY
Same as DAYOFYEAR.
DOW
DOW(datetime)
Operand type: datetime is a DATE or TIMESTAMP
Returns the day of the week as a 3‑character string.
DOW('2011-12-25') returns Sun.
EXTRACT
EXTRACT (part FROM datetime)
Operand type: datetime can be a DATE, TIME, TIMESTAMP, or INTERVAL value expression.
Result type: INTEGER
Extracts a particular field from a date/time value. Part specifies the field to extract.
A part parameter must be specified using a quoted string (for example: 'YEAR'). The parameter and is case sensitive.
Valid values for part are:
YEAR
Year field. Range: 0 - 9999
MONTH
Month field. Range: 1 - 12
DAY
Day field. Range: 1 - 31
HOUR
Hour field. Range: 0 - 23
MINUTE
Minute field. Range: 0 - 59
SECOND
Second field. Range: 0 - 59
MILLISECONDS
Fractional seconds as milliseconds. Range: 0 - 999.
MICROSECONDS
Fractional seconds as microseconds. Range: 0 - 999.
NANOSECONDS
Fractional seconds as nanoseconds. Range: 0 - 999.
TIMEZONE_HOUR
Time zone hour offset. Range: ‑12 ‑ 14.
TIMEZONE_MINUTE
Time zone minute offset. Range: 0 - 59
DAYOFWEEK
Day of week with Sunday=1. Range: 1 - 7.
DOW
Day of week with Sunday=1. Range: 1 - 7.
DAYOFYEAR
Day of year. Range: 1 - 366.
DOY
Day of year. Range: 1 - 366.
WEEK
Week of year. Range: 1 - 53.
ISO_WEEK
Week of year ISO 6801.
QUARTER
Year quarter. Range: 1 - 4.
EPOCH
UNIX timestamp (number of seconds since 1-Jan-1970).
Note: The datetime value cannot be an interval type on the TIMEZONE_HOUR, TIMEZONE_MINUTE, DAYOFWEEK, DAYOFYEAR, WEEK, ISO_WEEK, QUARTER, and EPOCH functions.
Examples:
SELECT EXTRACT (YEAR FROM datecol) FROM datetable;
SELECT EXTRACT (MONTH FROM CURRENT_DATE);
SELECT EXTRACT (HOUR FROM timecol) FROM datetable;
SELECT * FROM tx WHERE EXTRACT(HOUR FROM datetable) = 17;
FROM_UNIXTIME
Operand types: INTEGER, character string
Formats UNIX timestamp as a date.
FROM_UNIXTIME
FROM_UNIXTIME(i)
Result type: TIMESTAMP WITHOUT TIME ZONE
Returns TIMESTAMP WITHOUT TIME ZONE created from the specified integer, which must be a UNIX time (number of seconds since 1-Jan-1970).
FROM_UNIXTIME(i, format)
Result type: VARCHAR
Returns i formatted in UNIX time, according to the specified format. For valid formats, see DATE_FORMAT.
FROM_UNIXTIME(1196440219)
returns 2007-11-30 10:30:19.000000
FROM_UNIXTIME(1196440219, '%Y %D %M %h:%i:%s %x')
returns 2007 30th November 10:30:19 2007
Note: Results from this function are from a GMT offset, which may result in a value that is from '1969-12-31 12:00:00.000000' depending on the actual time zone of the server.
GMT_TIMESTAMP
GMT_TIMESTAMP(s)
Result type: Any character data type
Returns a twenty-three-character string giving the date s seconds after January 1, 1970 GMT. The output format is ‘yyyy_mm_dd hh:mm:ss GMT’.
For example, the query:
SELECT (GMT_TIMESTAMP (1234567890))
returns:
2009_02_13 23:31:30 GMT
while the query:
(II_TIMEZONE_NAME = AUSTRALIA-QUEENSLAND)
SELECT date(GMT_TIMESTAMP (1234567890))
returns:
14-feb-2009 09:31:30
HOUR
HOUR('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the hour portion of a time or timestamp.
HOUR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12
INTERVAL
INTERVAL(unit,date_interval)
Result type: FLOAT
Converts a date interval into a floating-point constant expressed in the unit of measurement specified by unit. The interval function assumes that there are 30.436875 days per month and 365.2425 days per year when using the month, quarter, and year specifications.
For example, the query:
SELECT(INTERVAL('days', '5 years'))
returns:
1826.213
ISDST
ISDST(date)
Returns true if date occurs during Daylight Saving Time in its time zone.
LAST_DAY
LAST_DAY(datetime)
Operand type: DATE, TIMESTAMP
Result type: DATE or TIMESTAMP depending on input
Returns the last day of the month in which the specified date or timestamp falls. Returns NULL if the argument is invalid.
LAST_DAY(DATE '2003-02-05')
returns 2003-02-28
LAST_DAY(DATE '2004-02-05')
returns 2004-02-29
LAST_DAY(TIMESTAMP '2004-01-01 01:01:01')
returns 2004-01-31
LAST_DAY(DATE '2003-03-32')
returns NULL
MICROSECOND
MICROSECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER4
Extracts the fractions of seconds portion of a time or timestamp as microseconds.
MICROSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400
MILLISECOND
MILLISECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER4
Extracts the fractions of seconds portion of a time or timestamp as milliseconds.
MILLISECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123
MINUTE
MINUTE('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the minute portion of a time or timestamp.
MINUTE(TIMESTAMP '2006-12-15 12:30:55.1234') returns 30
MONTH
MONTH('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the month portion of a date or timestamp.
MONTH(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12
MONTHS_BETWEEN
MONTHS_BETWEEN
MONTH_BETWEEN(date1, date2)
Operand type: DATE, TIMESTAMP
Result type: FLOAT8
Returns the number of months between date1 and date2, positive if date2 precedes date1 and negative if date1 precedes date2. If the day of date1 is the same as the day in date2 or both dates are the last days of months, then the result is a whole number of months; otherwise, any time portion is also taken into consideration in the difference, where days are treated as 1/31 of a month.
SELECT MONTHS_BETWEEN(DATE‘2010-06-15’, DATE‘2011-06-15’);
-12.000
SELECT MONTHS_BETWEEN(DATE‘2011-06-15’, DATE‘2011-05-15’);
1.000
SELECT MONTHS_BETWEEN(DATE‘2011-06-30’, DATE‘2011-05-31’);
1.000
SELECT MONTHS_BETWEEN(DATE‘2011-06-15’, DATE‘2011-06-01’);
0.452
NANOSECOND
NANOSECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER4
Extracts the fractions of seconds portion of a time or timestamp as nanoseconds.
NANOSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400000
QUARTER
QUARTER('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the quarter of the calendar year that corresponds to the date or timestamp. Quarters are numbered 1 through 4.
QUARTER(TIMESTAMP '2006-12-15 12:30:55.1234') returns 4
SECOND
SECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the second portion of a time or timestamp.
SECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 55
UNIX_TIMESTAMP
UNIX_TIMESTAMP()
UNIX_TIMESTAMP([datetime])
Operand type: DATE, TIME, or TIMESTAMP
Result type: INTEGER
Returns a UNIX timestamp (number of seconds since 1 Jan 1970) for the current time (if no argument is specified) or for the specified date.
UNIX_TIMESTAMP(TIMESTAMP '2007-11-30 10:30:19') returns 1196440219
Note: This function returns a valid result up to the year 2038 because the result is always an integer4 value in the range 1 though MAX_I4.
WEEK
WEEK('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Returns the number of the week of the year that the date or timestamp refers to. Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. Weeks are numbered 1 to 53.
WEEK('2006-12-15 12:30:55.1234') returns 50
WEEK_ISO
WEEKISO('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER1
Extracts the number of the week of the year that the date or timestamp refers to, and conforms to ISO 8601 definition for number of the week. Week_iso begins on Monday, but the first week is the week that has the first Thursday of the year. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and date_part returns either 52 or 53.
WEEK_ISO is equivalent to WEEK() function with mode 3.
WEEK_ISO(TIMESTAMP '2006-12-15 12:30:55.1234') returns 50
YEAR
YEAR('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the year portion of a date or timestamp.
YEAR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 2006
_DATE
_DATE(s)
Result type: Any character data type
Returns a nine-character string giving the date s seconds after January 1, 1970 GMT. The output format is dd-mmm-yy.
For example, the query:
SELECT _DATE(123456)
returns:
2-jan-70
Note: This function formats a leading space for day values less than 10.
_DATE4
_DATE4(s)
Result type: Any character data type
Returns an eleven-character string giving the date s seconds after January 1, 1970 GMT. The output format is controlled by the II_DATE_FORMAT setting.
For example, with II_DATE_FORMAT set to US, the query:
SELECT _DATE4(123456)
returns:
02-jan-1970
while with II_DATE_FORMAT set to MULTINATIONAL, the query:
SELECT _DATE4(123456)
returns:
02/01/1970
_TIME
_TIME(s)
Result type: Any character data type
Returns a five-character string giving the time s seconds after January 1, 1970 GMT, which is then adjusted for your local time zone. The output format is hh:mm (seconds are truncated).
For example, the query:
SELECT _TIME(123456)
returns the value 02:17 for the NA-PACIFIC time zone.