Categories:

Date & Time Functions

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> )
Copy

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 is week (or any of its variations), the output is controlled by the WEEK_START session parameter.

    • When date_or_time_part is dayofweek or yearofweek (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 |
+----------------------------------------+
Copy

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 |
+-------------------------+----------------+
Copy

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 |
+-------------------------+------------------------+
Copy

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 |
+-------------------------+-----------------------------+
Copy