- Categories:
DATE_PART¶
Extracts the specified date or time part from a date, time, or timestamp.
- Alternatives:
EXTRACT , HOUR / MINUTE / SECOND , YEAR* / DAY* / WEEK* / MONTH / QUARTER
Syntax¶
DATE_PART( <date_or_time_part> , <date_or_time_expr> )
Returns¶
The data type of the return value is NUMBER.
Usage Notes¶
date_or_time_part
must be one of the values listed in Supported Date and Time Parts.When
date_or_time_part
isweek
(or any of its variations), the output is controlled by the WEEK_START session parameter.When
date_or_time_part
isdayofweek
oryearofweek
(or any of their variations), the output is controlled by the WEEK_OF_YEAR_POLICY and WEEK_START session parameters.
For more details, including examples, see Calendar Weeks and Weekdays.
date_or_time_expr
can be a date, time, or timestamp.
Examples¶
This shows a simple example of extracting part of a DATE
:
SELECT DATE_PART(QUARTER, '2013-05-08'::DATE); +----------------------------------------+ | DATE_PART(QUARTER, '2013-05-08'::DATE) | |----------------------------------------| | 2 | +----------------------------------------+
This shows an example of extracting part of a TIMESTAMP
:
SELECT TO_TIMESTAMP('2013-05-08T23:39:20.123-07:00') AS "TIME_STAMP1", DATE_PART(YEAR, "TIME_STAMP1") AS "EXTRACTED YEAR"; +-------------------------+----------------+ | TIME_STAMP1 | EXTRACTED YEAR | |-------------------------+----------------| | 2013-05-08 23:39:20.123 | 2013 | +-------------------------+----------------+
This shows an example of converting a TIMESTAMP to the number of seconds since the beginning of the Unix epoch (midnight January 1, 1970):
SELECT TO_TIMESTAMP('2013-05-08T23:39:20.123-07:00') AS "TIME_STAMP1", DATE_PART(EPOCH_SECOND, "TIME_STAMP1") AS "EXTRACTED EPOCH SECOND"; +-------------------------+------------------------+ | TIME_STAMP1 | EXTRACTED EPOCH SECOND | |-------------------------+------------------------| | 2013-05-08 23:39:20.123 | 1368056360 | +-------------------------+------------------------+
This shows an example of converting a TIMESTAMP to the number of milliseconds since the beginning of the Unix epoch (midnight January 1, 1970):
SELECT TO_TIMESTAMP('2013-05-08T23:39:20.123-07:00') AS "TIME_STAMP1", DATE_PART(EPOCH_MILLISECOND, "TIME_STAMP1") AS "EXTRACTED EPOCH MILLISECOND"; +-------------------------+-----------------------------+ | TIME_STAMP1 | EXTRACTED EPOCH MILLISECOND | |-------------------------+-----------------------------| | 2013-05-08 23:39:20.123 | 1368056360123 | +-------------------------+-----------------------------+