日期或时间戳函数的日期部分 - Amazon Redshift
秒、毫秒和微秒导致的结果差异CENTURY、DECADE 和 MIL 说明

日期或时间戳函数的日期部分

下表标识了作为以下函数参数接受的日期部分和时间部分的名称和缩写：

  • DATEADD

  • DATEDIFF

  • DATE_PART

  • EXTRACT

日期部分或时间部分 缩写
millennium、millennia mil、mils
century、centuries c、cent、cents
decade、decades dec、decs
纪元 epoch（由 EXTRACT 提供支持）
year、years y、yr、yrs
quarter、quarters qtr、qtrs
month、months mon,、mons
week、weeks

w

星期几

dayofweek、dow、dw、weekday（由 DATE_PART 和 EXTRACT 函数 提供支持）

返回 0–6 的整数（星期日是第一个数）。

注意

DOW 日期部分的运行方式与用于日期时间格式字符串的星期 (D) 日期部分不同。D 是基于 1–7 的整数，其中星期日是 1。有关更多信息，请参阅 日期时间格式字符串。
一年中的日期 dayofyear、doy、dy、yearday（由 EXTRACT提供支持）
day、days d
hour、hours h、hr、hrs
minute、minutes m、min、mins
second、seconds s、sec、secs
millisecond、milliseconds ms、msec、msecs、msecond、mseconds、millisec、millisecs、millisecon
microsecond、microseconds microsec、microsecs、microsecond、usecond、useconds、us、usec、usecs
timezone、timezone_hour、timezone_minute 由 EXTRACT 支持，仅用于带有时区的时间戳 (TIMESTAMPTZ)。

秒、毫秒和微秒导致的结果差异

当不同的日期函数指定秒、毫秒或微秒作为日期部分时，查询结果会出现细微差异：

  • EXTRACT 函数仅返回指定日期部分的整数，忽略较高级别和较低级别的日期部分。如果指定的日期部分为秒，则结果中不包括毫秒和微秒。如果指定的日期部分为毫秒，则不包括秒和微秒。如果指定的日期部分为微秒，则不包括秒和毫秒。

  • DATE_PART 函数返回时间戳的完整秒部分，无论指定的日期部分是什么，从而根据需要返回十进制值或整数。

例如，比较以下查询的结果：

create table seconds(micro timestamp);

insert into seconds values('2009-09-21 11:10:03.189717');

select extract(sec from micro) from seconds;
               
date_part
-----------
3
               
select date_part(sec, micro) from seconds;
   
pgdate_part
-------------
3.189717

CENTURY、EPOCH、DECADE 和 MIL 说明

CENTURY 或 CENTURIES

Amazon Redshift 将 CENTURY 解释为开始于 ###1 年并结束于 ###0 年：

select extract (century from timestamp '2000-12-16 12:21:13');
date_part
-----------
20

select extract (century from timestamp '2001-12-16 12:21:13');
date_part
-----------
21
EPOCH

Amazon Redshift 的 EPOCH 实施与独立于集群所在的时区的 1970-01-01 00:00:00.000000 相关。根据集群所在的时区，您可能需要按小时差来抵消结果。

以下示例演示了以下操作：

  1. 基于 EVENT 表创建名为 EVENT_EXAMPLE 的表。此 CREATE AS 命令使用 DATE_PART 函数创建日期列（预设情况下称为 PGDATE_PART），以存储每个事件的纪元值。

  2. 从 PG_TABLE_DEF 中选择 EVENT_EXAMPLE 的列和数据类型。

  3. 从 EVENT_EXAMPLE 表中选择 EVENTNAME、STARTTIME 和 PGDATE_PART，以查看不同的日期和时间格式。

  4. 按原样从 EVENT EXAMPLE 中选择 EVENTNAME 和 STARTTIME。使用 1 秒的时间间隔将 PGDATE_PART 中的周期值转换为不带时区的时间戳，并在名为 CONVERTED_TIMESTAMP 的列中返回结果。

create table event_example
as select eventname, starttime, date_part(epoch, starttime) from event;

select "column", type from pg_table_def where tablename='event_example';

     column    |            type
---------------+-----------------------------
 eventname     | character varying(200)
 starttime     | timestamp without time zone
 pgdate_part   | double precision
(3 rows)
select eventname, starttime, pgdate_part from event_example;

   eventname          |      starttime      | pgdate_part
----------------------+---------------------+-------------
 Mamma Mia!           | 2008-01-01 20:00:00 |  1199217600
 Spring Awakening     | 2008-01-01 15:00:00 |  1199199600
 Nas                  | 2008-01-01 14:30:00 |  1199197800
 Hannah Montana       | 2008-01-01 19:30:00 |  1199215800
 K.D. Lang            | 2008-01-01 15:00:00 |  1199199600
 Spamalot             | 2008-01-02 20:00:00 |  1199304000
 Macbeth              | 2008-01-02 15:00:00 |  1199286000
 The Cherry Orchard   | 2008-01-02 14:30:00 |  1199284200
 Macbeth              | 2008-01-02 19:30:00 |  1199302200
 Demi Lovato          | 2008-01-02 19:30:00 |  1199302200

   
select eventname, 
starttime, 
timestamp with time zone 'epoch' + pgdate_part * interval '1 second' AS converted_timestamp 
from event_example;

       eventname      |      starttime      | converted_timestamp
----------------------+---------------------+---------------------
 Mamma Mia!           | 2008-01-01 20:00:00 | 2008-01-01 20:00:00
 Spring Awakening     | 2008-01-01 15:00:00 | 2008-01-01 15:00:00
 Nas                  | 2008-01-01 14:30:00 | 2008-01-01 14:30:00
 Hannah Montana       | 2008-01-01 19:30:00 | 2008-01-01 19:30:00
 K.D. Lang            | 2008-01-01 15:00:00 | 2008-01-01 15:00:00
 Spamalot             | 2008-01-02 20:00:00 | 2008-01-02 20:00:00
 Macbeth              | 2008-01-02 15:00:00 | 2008-01-02 15:00:00
 The Cherry Orchard   | 2008-01-02 14:30:00 | 2008-01-02 14:30:00
 Macbeth              | 2008-01-02 19:30:00 | 2008-01-02 19:30:00
 Demi Lovato          | 2008-01-02 19:30:00 | 2008-01-02 19:30:00
 ...
DECADE 或 DECADES

Amazon Redshift 根据公历解释 DECADE 或 DECADES DATEPART。例如，由于公历从第一年开始，因此第一个十年（第 1 个十年）是 0001-01-01 到 0009-12-31，而第二个十年（第 2 个十年）是 0010-01-01 到 0019-12-31。例如，十年 201 为 2000-01-01 - 2009-12-31：

select extract(decade from timestamp '1999-02-16 20:38:40');
date_part
-----------
200

select extract(decade from timestamp '2000-02-16 20:38:40');
date_part
-----------
201

select extract(decade from timestamp '2010-02-16 20:38:40');
date_part
-----------
202
MIL 或 MILS

Amazon Redshift 将 MIL 解释为开始于 #001 年的第一天并结束于 #000 年的最后一天：

select extract (mil from timestamp '2000-12-16 12:21:13');
date_part
-----------
2

select extract (mil from timestamp '2001-12-16 12:21:13');
date_part
-----------
3