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

Copy

DATE_PART( <date_or_time_part> FROM <date_time_or_timestamp_expr> )

Copy

Arguments¶

date_or_time_part

The unit of time. Must be one of the values listed in Supported date and time parts (e.g. month). The value can be a string literal or can be unquoted (for example, 'month' or month).

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 information, including examples, see Calendar weeks and weekdays.

date_time_or_timestamp_expr: A date, a time, or a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp.

Returns¶

Returns a value of NUMBER data type.

Usage notes¶

Currently, when date_or_timestamp_expr is a DATE value, the following date_or_time_part values are not supported:

epoch_millisecond
epoch_microsecond
epoch_nanosecond

Other date and time parts (including epoch_second) are supported.

Tip

To extract a full DATE or TIME value instead of a single part from a TIMESTAMP value, you can cast the TIMESTAMP value to a DATE or TIME value, respectively. For example:

SELECT '2025-04-08T23:39:20.123-07:00'::TIMESTAMP::DATE AS full_date_value;

Copy

+-----------------+
| FULL_DATE_VALUE |
|-----------------|
| 2025-04-08      |
+-----------------+

SELECT '2025-04-08T23:39:20.123-07:00'::TIMESTAMP::TIME AS full_time_value;

Copy

+-----------------+
| FULL_TIME_VALUE |
|-----------------|
| 23:39:20        |
+-----------------+

Examples¶

This shows a simple example of extracting part of a DATE:

SELECT DATE_PART(quarter, '2024-04-08'::DATE);

Copy

+----------------------------------------+
| DATE_PART(QUARTER, '2024-04-08'::DATE) |
|----------------------------------------|
|                                      2 |
+----------------------------------------+

This shows an example of extracting part of a TIMESTAMP:

SELECT TO_TIMESTAMP(
  '2024-04-08T23:39:20.123-07:00') AS "TIME_STAMP1",
  DATE_PART(year, "TIME_STAMP1") AS "EXTRACTED YEAR";

Copy

+-------------------------+----------------+
| TIME_STAMP1             | EXTRACTED YEAR |
|-------------------------+----------------|
| 2024-04-08 23:39:20.123 |           2024 |
+-------------------------+----------------+

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(
  '2024-04-08T23:39:20.123-07:00') AS "TIME_STAMP1",
  DATE_PART(epoch_second, "TIME_STAMP1") AS "EXTRACTED EPOCH SECOND";

Copy

+-------------------------+------------------------+
| TIME_STAMP1             | EXTRACTED EPOCH SECOND |
|-------------------------+------------------------|
| 2024-04-08 23:39:20.123 |             1712619560 |
+-------------------------+------------------------+

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(
  '2024-04-08T23:39:20.123-07:00') AS "TIME_STAMP1",
  DATE_PART(epoch_millisecond, "TIME_STAMP1") AS "EXTRACTED EPOCH MILLISECOND";

Copy

+-------------------------+-----------------------------+
| TIME_STAMP1             | EXTRACTED EPOCH MILLISECOND |
|-------------------------+-----------------------------|
| 2024-04-08 23:39:20.123 |               1712619560123 |
+-------------------------+-----------------------------+