类别：

日期和时间函数

DATE_PART

从日期、时间或时间戳中提取指定的日期或时间部分。

选择：

EXTRACT、HOUR / MINUTE / SECOND、YEAR* / DAY* / WEEK* / MONTH / QUARTER

语法

DATE_PART( <date_or_time_part> , <date_interval_time_or_timestamp_expr> )

DATE_PART( <date_or_time_part> FROM <date_interval_time_or_timestamp_expr> )

实参

date_or_time_part

The unit of time. Must be one of the values listed in 支持的日期和时间部分 (for example, month). The value can be a string literal or can be unquoted (for example, 'month' or month).

  • 当 date_or_time_part 为 week`（或其任何变体）时，输出由 :ref:`label-week_start 会话参数控制。

  • 当 date_or_time_part 为 dayofweek 或 yearofweek`（或其任何变体）时，输出由 :ref:`label-week_of_year_policy 和 WEEK_START 会话参数控制。

有关更多信息（包括示例），请参阅 日历周和工作日。

date_interval_time_or_timestamp_expr

A date, an interval, a time, or a timestamp, or an expression that can be evaluated to one of those data types.

返回

返回 NUMBER 数据类型的值。

使用说明

  • When date_interval_time_or_timestamp_expr is a year-month interval value, the supported date_or_time_part values are year and month.

  • When date_interval_time_or_timestamp_expr is a day-time interval value, the supported date_or_time_part values are day, hour, minute, second, and nanosecond.

  • Currently, when date_interval_time_or_timestamp_expr is a DATE value, the following date_or_time_part values aren't supported:

    • epoch_millisecond

    • epoch_microsecond

    • epoch_nanosecond

    支持其他 日期和时间部分 （包括 epoch_second）。

小技巧

要从 TIMESTAMP 值中提取完整的 DATE 或 TIME 值（而非单个部分），可相应地将 TIMESTAMP 值转换为 DATE 或 TIME 值。例如：

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

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

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

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

示例

这显示了提取 DATE 部分内容的简单示例：

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

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

这显示了提取 TIMESTAMP 部分内容的示例：

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

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

这显示了将 TIMESTAMP 转换为自 Unix 纪元 (link removed) 开始（1970 年 1 月 1 日午夜）以来秒数的示例：

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

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

这显示了将 TIMESTAMP 转换为自 Unix 纪元 (link removed) 开始（1970 年 1 月 1 日午夜）以来毫秒数的示例：

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

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