9.9. 日期/时间函数和运算符

Table 9.30显示了日期/时间值处理的可用功能,详细信息显示在以下小节中。 Table 9.29说明了基本算术运算符(+*等)的行为。有关格式化功能,请参考Section 9.8。您应该熟悉Section 8.5中有关日期/时间数据类型的背景信息。

下文所述的所有接受timetimestampImporting 的函数和运算符实际上有两种变体:一种采用time with time zonetimestamp with time zone,一种采用time without time zonetimestamp without time zone。为简便起见,这些变体未单独显示。同样,+*运算符成对交换(例如,日期整数和整数日期);我们只显示每对这样的一对。

表 9.29. 日期/时间运算符

OperatorExampleResult
+date '2001-09-28' + integer '7'date '2001-10-05'
+date '2001-09-28' + interval '1 hour'timestamp '2001-09-28 01:00:00'
+date '2001-09-28' + time '03:00'timestamp '2001-09-28 03:00:00'
+interval '1 day' + interval '1 hour'interval '1 day 01:00:00'
+timestamp '2001-09-28 01:00' + interval '23 hours'timestamp '2001-09-29 00:00:00'
+time '01:00' + interval '3 hours'time '04:00:00'
-- interval '23 hours'interval '-23:00:00'
-date '2001-10-01' - date '2001-09-28'integer '3'(天)
-date '2001-10-01' - integer '7'date '2001-09-24'
-date '2001-09-28' - interval '1 hour'timestamp '2001-09-27 23:00:00'
-time '05:00' - time '03:00'interval '02:00:00'
-time '05:00' - interval '2 hours'time '03:00:00'
-timestamp '2001-09-28 23:00' - interval '23 hours'timestamp '2001-09-28 00:00:00'
-interval '1 day' - interval '1 hour'interval '1 day -01:00:00'
-timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'interval '1 day 15:00:00'
*900 * interval '1 second'interval '00:15:00'
*21 * interval '1 day'interval '21 days'
*double precision '3.5' * interval '1 hour'interval '03:30:00'
/interval '1 hour' / double precision '1.5'interval '00:40:00'

表 9.30. 日期/时间功能

FunctionReturn TypeDescriptionExampleResult
age(timestamp, timestamp)interval减去参数,产生使用数年而不是数天的“符号”结果age(timestamp '2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days
age(timestamp)intervalcurrent_date减去(在午夜)age(timestamp '1957-06-13')43 years 8 mons 3 days
clock_timestamp()timestamp with time zone当前日期和时间(语句执行期间的更改);见Section 9.9.4
current_datedate当前日期;见Section 9.9.4
current_timetime with time zone当前时间;见Section 9.9.4
current_timestamptimestamp with time zone当前日期和时间(当前 Transaction 的开始);见Section 9.9.4
date_part(text, timestamp)double precision获取子字段(相当于extract);见Section 9.9.1date_part('hour', timestamp '2001-02-16 20:38:40')20
date_part(text, interval)double precision获取子字段(相当于extract);见Section 9.9.1date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp)timestamp截断为指定的精度;另请参阅Section 9.9.2date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00
date_trunc(text, interval)interval截断为指定的精度;另请参阅Section 9.9.2date_trunc('hour', interval '2 days 3 hours 40 minutes')2 days 03:00:00
extract(field from timestamp)double precision获取子字段;见Section 9.9.1extract(hour from timestamp '2001-02-16 20:38:40')20
extract(field from interval)double precision获取子字段;见Section 9.9.1extract(month from interval '2 years 3 months')3
isfinite(date)boolean测试有限日期(非/无限)isfinite(date '2001-02-16')true
isfinite(timestamp)boolean测试有限时间戳记(非/无穷大)isfinite(timestamp '2001-02-16 21:28:30')true
isfinite(interval)boolean测试有限间隔isfinite(interval '4 hours')true
justify_days(interval)interval调整间隔,使 30 天的时间段表示为月justify_days(interval '35 days')1 mon 5 days
justify_hours(interval)interval调整时间间隔,使 24 小时制以天数表示justify_hours(interval '27 hours')1 day 03:00:00
justify_interval(interval)interval使用justify_daysjustify_hours调整间隔,并进行其他符号调整justify_interval(interval '1 mon -1 hour')29 days 23:00:00
localtimetime当前时间;见Section 9.9.4
localtimestamptimestamp当前日期和时间(当前 Transaction 的开始);见Section 9.9.4
make_date(year int, month int, day int)date根据年,月和日字段创建日期make_date(2013, 7, 15)2013-07-15
make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)interval从年,月,周,天,小时,分钟和秒字段创建间隔make_interval(days => 10)10 days
make_time(hour int, min int, sec double precision)time从小时,分钟和秒字段创建时间make_time(8, 15, 23.5)08:15:23.5
make_timestamp(year int, month int, day int, hour int, min int, sec double precision)timestamp从年,月,日,小时,分钟和秒字段创建时间戳make_timestamp(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5
make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])timestamp with time zone使用年,月,日,小时,分钟和秒字段中的时区创建时间戳;如果未指定* timezone *,则使用当前时区make_timestamptz(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5+01
now()timestamp with time zone当前日期和时间(当前 Transaction 的开始);见Section 9.9.4
statement_timestamp()timestamp with time zone当前日期和时间(当前语句的开始);见Section 9.9.4
timeofday()text当前日期和时间(如clock_timestamp,但为text字符串);见Section 9.9.4
transaction_timestamp()timestamp with time zone当前日期和时间(当前 Transaction 的开始);见Section 9.9.4
to_timestamp(double precision)timestamp with time zone将 Unix 纪元(自 1970-01-01 00:00:00 00 起的秒数)转换为时间戳to_timestamp(1284352323)2010-09-13 04:32:03+00

除了这些功能外,还支持 SQL OVERLAPS运算符:

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

当两个时间段(由它们的端点定义)重叠时,此表达式将产生 true;当两个时间段不重叠时,该表达式将产生 false。可以将端点指定为日期,时间或时间戳记对。或作为日期,时间或时间戳记,后跟一个间隔。提供一对值时,可以先写开始或结束。 OVERLAPS自动将对中的较早值作为开始。每个时间段都被视为代表半开间隔* start * <= * time * < * end ,除非 start end *相等,在这种情况下,它表示该单个 Moment。例如,这意味着只有一个共同点的两个时间段不会重叠。

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: true

当向timestamp with time zone值添加interval值(或从中减去interval值)时,天组成部分会将timestamp with time zone的日期提前或减少指定的天数。跨夏令时更改(将会话时区设置为可识别 DST 的时区),这意味着interval '1 day'不一定等于interval '24 hours'。例如,如果将会话时区设置为CST7CDT,则timestamp with time zone '2005-04-02 12:00-07' + interval '1 day'将产生timestamp with time zone '2005-04-03 12:00-06',而将interval '24 hours'添加到同一初始timestamp with time zone会产生timestamp with time zone '2005-04-03 13:00-06',因为时区CST7CDT2005-04-03 02:00的夏时制发生了变化。

请注意,由于不同月份的天数不同,因此age返回的months字段中可能会有歧义。 PostgreSQL 的方法在计算部分月份时使用两个日期中较早日期的月份。例如,age('2004-06-01', '2004-04-30')使用 April 产生1 mon 1 day,而使用 May 将产生1 mon 2 days,因为 May 拥有 31 天,而 April 仅有 30 天。

日期和时间戳的相减也可能很复杂。从概念上讲,执行减法的一种简单方法是使用EXTRACT(EPOCH FROM ...)将每个值转换为秒数,然后减去结果。这将在两个值之间产生的数量。这将根据每月的天数,时区更改和夏时制进行调整。用“ -”运算符减去日期或时间戳记值将返回值之间的天数(24 小时)和小时/分钟/秒,进行相同的调整。 age函数返回年,月,日和小时/分钟/秒,执行逐字段减法,然后针对负字段值进行调整。以下查询说明了这些方法的差异。samples 结果为timezone = 'US/Eastern';在使用的两个日期之间存在夏令时更改:

SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
       EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Result: 10537200
SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
        EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
        / 60 / 60 / 24;
Result: 121.958333333333
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
Result: 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
Result: 4 mons

9 .9.1. 摘录,date_part

EXTRACT(field FROM source)

extract函数从日期/时间值中检索子字段,例如年或小时。 * source 必须是timestamptimeinterval类型的值表达式。 (类型date的表达式被强制转换为timestamp,因此也可以使用.) field *是标识符或字符串,用于选择要从源值提取的字段。 extract函数返回double precision类型的值。以下是有效的字段名称:

  • century

    • The century
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
Result: 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 21

第一世纪始于公元 0001-01-01 00:00:00,尽管当时他们还不知道。此定义适用于所有公历国家。没有世纪数字 0,您从-1 世纪到 1 世纪。如果您不同意此请求,请将您的投诉写给:梵蒂冈罗马大教堂的圣彼得教皇。

  • day

    • 对于timestamp值,(月)的天字段(1-31);对于interval个值,为天数
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
Result: 40
  • decade

    • 年字段除以 10
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 200
  • dow

    • 星期几是星期日(0)至星期六(6)
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 5

请注意,extract的星期几编号与to_char(..., 'D')函数的星期几编号不同。

  • doy

    • 一年中的一天(1-365/366)
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 47
  • epoch

    • 对于timestamp with time zone值,是自 1970-01-01 00:00:00 UTC 以来的秒数(可以为负);对于datetimestamp值,是自本地时间 1970-01-01 00:00:00 以来的秒数;对于interval值,间隔中的总秒数
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
Result: 982384720.12

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800

您可以使用to_timestamp将纪元值转换回时间戳记:

SELECT to_timestamp(982384720.12);
Result: 2001-02-17 04:38:40.12+00
  • hour

    • 小时字段(0-23)
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20
  • isodow

    • 星期几为星期一(1)至星期日(7)
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
Result: 7

除星期天外,其余与dow相同。这与 ISO 8601 的星期几编号相匹配。

  • isoyear

    • 日期所属的 ISO 8601 周编号年份(不适用于间隔)
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
Result: 2005
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
Result: 2006

每个 ISO 8601 周编号的年份都从包含 1 月 4 日的一周的星期一开始,因此 ISO 年份的 1 月初或 12 月下旬可能不同于格里高利历年。有关更多信息,请参见week字段。

在 8.3 之前的 PostgreSQL 版本中,此字段不可用。

  • microseconds

    • 秒字段(包括小数部分)乘以 1 000 000;请注意,这包括整秒钟
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Result: 28500000
  • millennium

    • The millennium
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 3

1900 年代是第二个千年。第三个千年始于 2001 年 1 月 1 日。

  • milliseconds

    • 秒字段(包括小数部分)乘以 1000.请注意,这包括整秒。
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
Result: 28500
  • minute

    • 分钟字段(0-59)
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 38
  • month

    • 对于timestamp值,是一年中的月份数(1-12);对于interval值,月数,模 12(0-11)
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
Result: 3

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
Result: 1
  • quarter

    • 该日期所在的一年的四分之一(1-4)
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 1
  • second

    • 秒字段,包括小数部分(0-59 [7])
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 40

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Result: 28.5
  • timezone

    • 与 UTC 的时区偏移量,以秒为单位。正值对应于 UTC 以东的时区,负值对应于 UTC 以西的时区。 (从技术上讲,由于不处理 leap 秒,PostgreSQL 不使用 UTC.)
  • timezone_hour

    • 时区偏移的小时部分
  • timezone_minute

    • 时区偏移的分钟部分
  • week

    • ISO 8601 周编号,是一年中的第几周。根据定义,ISO 周从星期一开始,一年的第一周包含该年的 1 月 4 日。换句话说,一年的第一个星期四在该年的第 1 周。

在 ISO 周编号系统中,一月初的日期可能是上一年的第 52 或 53 周的一部分,而十二月末的日期可能是下一年的第一周的一部分。例如,2005-01-01是 2004 年第 53 周的一部分,而2006-01-01是 2005 年第 52 周的一部分,而2012-12-31是 2013 年第一周的一部分。建议将isoyear字段与week一起使用结果。

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 7
  • year

    • 年字段。请记住,没有0 AD,因此应谨慎地从AD年减去BC年。
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2001

Note

当 Importing 值为/ -Infinity 时,extract对于单调递增的字段(epochjulianyearisoyeardecadecenturymillennium)返回/ -Infinity。对于其他字段,返回 NULL。对于无限 Importing 的所有情况,9.6 之前的 PostgreSQL 版本返回零。

extract函数主要用于计算处理。有关格式化显示日期/时间值的信息,请参见Section 9.8

date_part函数以等效于 SQL 标准函数extract的传统 Ingres 为模型:

date_part('field', source)

请注意,此处的field *参数必须是字符串值,而不是名称。 date_part的有效字段名称与extract的相同。

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4

9.9.2. date_trunc

函数date_trunc在概念上类似于数字的trunc函数。

date_trunc('field', source)
  • source 是类型timestampinterval的值表达式。 (类型datetime的值分别自动转换为timestampinterval.) field *选择截断 Importing 值的精度。返回值的类型为timestampinterval,所有比所选字段低的字段都设置为零(对于日和月,则设置为零)。

  • field *的有效值为:

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

Examples:

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00

9 .9.3. 时区

AT TIME ZONE将没有时区的时间戳转换为带有时区的时间戳,以及将* time *值转换为不同时区。 Table 9.31显示了其变体。

表 9.31. AT TIME ZONE变体

ExpressionReturn TypeDescription
timestamp without time zone AT TIME ZONE zonetimestamp with time zone将给定时间戳记不带时区视为位于指定时区
timestamp with time zone AT TIME ZONE zonetimestamp without time zone将给定时间戳记带有时区转换为新时区,而无需指定时区
time with time zone AT TIME ZONE zonetime with time zone将给定时间带有时区转换为新时区

在这些表达式中,可以将所需时区* zone *指定为文本字符串(例如'America/Los_Angeles')或间隔(例如INTERVAL '-08:00')。在文本情况下,可以使用Section 8.5.3中描述的任何方式指定时区名称。

示例(假设本地时区是America/Los_Angeles):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 18:38:40

SELECT TIMESTAMP '2001-02-16 20:38:40-05' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Result: 2001-02-16 05:38:40

第一个示例向缺少时区的值添加时区,并使用当前的TimeZone设置显示该值。第二个示例将带有时区值的时间戳移到指定的时区,并返回不带时区的值。这样可以存储和显示与当前TimeZone设置不同的值。第三个示例将东京时间转换为芝加哥时间。由于没有提供日期,因此将* time *值转换为其他时区会使用当前活动的时区规则。

函数timezone(zone, timestamp)等效于符合 SQL 的构造timestamp AT TIME ZONE zone

9 .9.4. 当前日期/时间

PostgreSQL 提供了许多函数,这些函数返回与当前日期和时间有关的值。这些 SQL 标准函数均基于当前事务的开始时间返回值:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

CURRENT_TIMECURRENT_TIMESTAMP按时区传递值; LOCALTIMELOCALTIMESTAMP传递没有时区的值。

CURRENT_TIMECURRENT_TIMESTAMPLOCALTIMELOCALTIMESTAMP可以选择采用 precision 参数,这将导致结果四舍五入为秒字段中的小数位数。如果没有精度参数,则将结果赋予全部可用精度。

Some examples:

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Result: 2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Result: 2001-12-23 14:39:53.662522

由于这些函数返回当前事务的开始时间,因此它们的值在事务期间不会更改。这被认为是一个功能:目的是允许单个事务具有“当前”时间的一致概念,以便同一事务内的多个修改具有相同的时间戳。

Note

其他数据库系统可能会更频繁地提高这些值。

PostgreSQL 还提供了返回当前语句开始时间的函数,以及该函数被调用时的实际当前时间。非 SQL 标准时间函数的完整列表为:

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

transaction_timestamp()等效于CURRENT_TIMESTAMP,但命名为是为了清楚地反映其返回的内容。 statement_timestamp()返回当前语句的开始时间(更具体地说,是从 Client 端接收最新命令消息的时间)。 statement_timestamp()transaction_timestamp()在事务的第一个命令期间返回相同的值,但在后续命令期间可能有所不同。 clock_timestamp()返回实际的当前时间,因此,即使在单个 SQL 命令中,其值也会更改。 timeofday()是 PostgreSQL 的历史功能。像clock_timestamp()一样,它返回实际的当前时间,但以格式化的text字符串而不是timestamp with time zone值形式返回。 now()是与transaction_timestamp()等效的传统 PostgreSQL。

所有日期/时间数据类型也接受特殊 Literals 值now以指定当前日期和时间(再次解释为事务开始时间)。因此,以下三个都返回相同的结果:

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';  -- incorrect for use with DEFAULT

Tip

在创建表时指定DEFAULT子句时,您不想使用第三种形式。解析常量后,系统会立即将now转换为timestamp,以便在需要默认值时将使用创建表的时间!在使用默认值之前,不会评估前两种形式,因为它们是函数调用。因此,它们将提供默认的行插入时间默认值。

9 .9.5. 延迟执行

以下功能可用于延迟服务器进程的执行:

pg_sleep(seconds)
pg_sleep_for(interval)
pg_sleep_until(timestamp with time zone)

pg_sleep使当前会话的进程处于睡眠状态,直到* seconds *秒过去。 * seconds *是double precision类型的值,因此可以指定小数秒延迟。 pg_sleep_for是指定为interval的较大睡眠时间的便利功能。 pg_sleep_until是方便的功能,用于需要特定的唤醒时间。例如:

SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');

Note

睡眠间隔的有效分辨率是特定于平台的; 0.01 秒是一个常用值。睡眠延迟将至少与指定的时间一样长。可能会更长,具体取决于服务器负载等因素。特别是,不能保证pg_sleep_until在指定的时间完全唤醒,但不会更早地唤醒。

Warning

确保调用pg_sleep或其变体时,会话中没有持有过多的锁。否则,其他会话可能必须 await 您的睡眠过程,从而降低整个系统的速度。


[7] 60(如果 os 实施了 leap 秒)