Postgresql 中文操作指南

9.9. Date/Time Functions and Operators #

Table 9.33显示了可用于日期/时间值处理的可用函数，详细信息见以下小节中。 Table 9.32说明了基本算术运算符（+、_*_等）的行为。有关格式化函数，请参阅 Section 9.8。您应该熟悉 Section 8.5中有关日期/时间数据类型的背景信息。

此外， Table 9.1中所示的常用比较运算符可用于日期/时间类型。日期和时间戳（带有或不带有时区）都是可比较的，而时间（带有或不带有时区）和时间间隔只能与同一数据类型的其他值进行比较。在将没有时区的时间戳与带有时区的时间戳进行比较时，会假设前者使用 TimeZone配置参数指定时区给出，并且会将其旋转到 UTC 以便与后者值（内部已经在 UTC 中）进行比较。类似地，在将日期值与时间戳进行比较时，会假设一个日期值表示 _TimeZone_时区的午夜。

下面描述的所有函数和运算符采用 time 或 timestamp 作为输入时实际上分为两个变体：一种采用 time with time zone 或 timestamp with time zone，另一种采用 time without time zone 或 timestamp without time zone。为简便起见，这些变体不单独显示。此外，+ 和 * 运算符呈交换对（例如 date + integer 和 integer + date）；我们仅显示每一对中的一个。

Table 9.32. Date/Time Operators

Operator

Description

Example(s)

date + integer → date 为日期添加天数 date '2001-09-28' + 7 → 2001-10-05

date + interval → timestamp 向日期中添加区间 date '2001-09-28' + interval '1 hour' → 2001-09-28 01:00:00

date + time → timestamp 将时间添加到日期 date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00

interval + interval → interval 添加间隔 interval '1 day' + interval '1 hour' → 1 day 01:00:00

timestamp + interval → timestamp 将间隔添加到时间戳 timestamp '2001-09-28 01:00' + interval '23 hours' → 2001-09-29 00:00:00

time + interval → time 将间隔添加到时间 time '01:00' + interval '3 hours' → 04:00:00

- interval → interval 否定一个间隔 - interval '23 hours' → -23:00:00

date - date → integer 减去日期，产生流逝的天数 date '2001-10-01' - date '2001-09-28' → 3

date - integer → date 从日期中减去一定的天数 date '2001-10-01' - 7 → 2001-09-24

date - interval → timestamp 从日期中减去一个间隔 date '2001-09-28' - interval '1 hour' → 2001-09-27 23:00:00

time - time → interval 减去时间 time '05:00' - time '03:00' → 02:00:00

time - interval → time 从时间中减去一个间隔 time '05:00' - interval '2 hours' → 03:00:00

timestamp - interval → timestamp 从时间戳中减去一个间隔 timestamp '2001-09-28 23:00' - interval '23 hours' → 2001-09-28 00:00:00

interval - interval → interval 减去间隔 interval '1 day' - interval '1 hour' → 1 day -01:00:00

timestamp - timestamp → interval 减去时间戳（将 24 小时间隔转换成天，类似于 justify_hours() ） timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' → 63 days 15:00:00

interval * double precision → interval 用标量乘以间隔 interval '1 second' * 900 → 00:15:00 interval '1 day' * 21 → 21 days interval '1 hour' * 3.5 → 03:30:00

interval / double precision → interval 用标量除以间隔 interval '1 hour' / 1.5 → 00:40:00

Table 9.33. Date/Time Functions

Function

Description

Example(s)

age ( timestamp , timestamp ) → interval 减去参数，产生一个“象征性”结果，该结果使用年和月，而不仅仅是天 age(timestamp '2001-04-10', timestamp '1957-06-13') → 43 years 9 mons 27 days

age ( timestamp ) → interval 从 current_date (午夜) 减去参数 age(timestamp '1957-06-13') → 62 years 6 mons 10 days

clock_timestamp ( ) → timestamp with time zone 当前日期和时间（在语句执行期间更改）；参见 Section 9.9.5 clock_timestamp() → 2019-12-23 14:39:53.662522-05

current_date → date 当前日期；参见 Section 9.9.5 current_date → 2019-12-23

current_time → time with time zone 当前时间；另请参见 Section 9.9.5 current_time → 14:39:53.662522-05

current_time ( integer ) → time with time zone 当前时间（精度有限）；另请参见 Section 9.9.5 current_time(2) → 14:39:53.66-05

current_timestamp → timestamp with time zone 当前日期和时间（当前交易的开始时间）；另请参见 Section 9.9.5 current_timestamp → 2019-12-23 14:39:53.662522-05

current_timestamp ( integer ) → timestamp with time zone 当前日期和时间（当前交易的开始时间），精度有限；另请参见 Section 9.9.5 current_timestamp(0) → 2019-12-23 14:39:53-05

date_add ( timestamp with time zone , interval [, text ] ) → timestamp with time zone 将 interval 添加到 timestamp with time zone ，根据第三个参数指定的时区计算一天中的时间和日光节约时间调整，如果第三个参数中没有内容，则会计算当前 TimeZone 的设置。有两个参数的形式等同于 timestamp with time zone + interval 操作符。 date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw') → 2021-10-31 23:00:00+00

date_bin ( interval , timestamp , timestamp ) → timestamp 将输入按照指定的原点对齐，划分为指定的时间间隔；另请参见 Section 9.9.3 date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00') → 2001-02-16 20:35:00

date_part ( text , timestamp ) → double precision 获取时间戳子字段（等同于 extract ）；另请参见 Section 9.9.1 date_part('hour', timestamp '2001-02-16 20:38:40') → 20

date_part ( text , interval ) → double precision 获取时间间隔子字段（等同于 extract ）；另请参见 Section 9.9.1 date_part('month', interval '2 years 3 months') → 3

date_subtract ( timestamp with time zone , interval [, text ] ) → timestamp with time zone 从 timestamp with time zone 中减去 interval ，根据第三个参数指定的时区计算一天中的时间和日光节约时间调整，如果第三个参数中没有内容，则会计算当前 TimeZone 的设置。有两个参数的形式等同于 timestamp with time zone - interval 操作符。 date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw') → 2021-10-30 22:00:00+00

date_trunc ( text , timestamp ) → timestamp 截断为指定的精确度；另请参见 Section 9.9.2 date_trunc('hour', timestamp '2001-02-16 20:38:40') → 2001-02-16 20:00:00

date_trunc ( text , timestamp with time zone , text ) → timestamp with time zone 按照指定的时区截断为指定的精确度；另请参见 Section 9.9.2 date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') → 2001-02-16 13:00:00+00

date_trunc ( text , interval ) → interval 截断为指定的精确度；另请参见 Section 9.9.2 date_trunc('hour', interval '2 days 3 hours 40 minutes') → 2 days 03:00:00

extract ( field from timestamp ) → numeric 获取时间戳子字段；另请参见 Section 9.9.1 extract(hour from timestamp '2001-02-16 20:38:40') → 20

extract ( field from interval ) → numeric 获取时间间隔子字段；另请参见 Section 9.9.1 extract(month from interval '2 years 3 months') → 3

isfinite ( date ) → boolean 测试日期是否为有限日期（非 +/- 无穷大） isfinite(date '2001-02-16') → true

isfinite ( timestamp ) → boolean 测试时间戳是否为有限时间戳（非 +/- 无穷大） isfinite(timestamp 'infinity') → false

isfinite ( interval ) → boolean 测试时间间隔是否为有限时间间隔（当前始终为真） isfinite(interval '4 hours') → true

justify_days ( interval ) → interval 调整时间间隔，将 30 天时间段转换为月份 justify_days(interval '1 year 65 days') → 1 year 2 mons 5 days

justify_hours ( interval ) → interval 调整时间间隔，将 24 小时时间段转换为天数 justify_hours(interval '50 hours 10 minutes') → 2 days 02:10:00

justify_interval （ interval ）→ interval 使用 justify_days 和 justify_hours 调整时间间隔，并进行额外的符号调整 justify_interval(interval '1 mon -1 hour') → 29 days 23:00:00

localtime → time 一天中的当前时间；参见 Section 9.9.5 localtime → 14:39:53.662522

localtime （ integer ）→ time 一天中的当前时间（精度有限）；参见 Section 9.9.5 localtime(0) → 14:39:53

localtimestamp → timestamp 当前日期和时间（当前事务的开始）；参见 Section 9.9.5 localtimestamp → 2019-12-23 14:39:53.662522

localtimestamp （ integer ）→ timestamp 当前日期和时间（当前事务的开始，精度有限）；参见 Section 9.9.5 localtimestamp(2) → 2019-12-23 14:39:53.66

make_date （ year int ， month int ， day int ）→ date 根据年、月和日字段创建日期（负年表示公元前） make_date(2013, 7, 15) → 2013-07-15

make_interval （[ years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]]）→ 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 根据年、月、日、时、分和秒字段创建带有时区的timestamp（负年表示公元前）。如果未指定 timezone ，则使用当前时区；这些示例假定会话时区为 Europe/London make_timestamptz(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5+01 make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York') → 2013-07-15 13:15:23.5+01

now （）→ timestamp with time zone 当前日期和时间（当前事务的开始）；参见 Section 9.9.5 now() → 2019-12-23 14:39:53.662522-05

statement_timestamp （）→ timestamp with time zone 当前日期和时间（当前语句的开始）；参见 Section 9.9.5 statement_timestamp() → 2019-12-23 14:39:53.662522-05

timeofday （）→ text 当前日期和时间（类似 clock_timestamp ，但为 text 字符串）；参见 Section 9.9.5 timeofday() → Mon Dec 23 14:39:53.662522 2019 EST

transaction_timestamp （）→ timestamp with time zone 当前日期和时间（当前事务的开始）；参见 Section 9.9.5 transaction_timestamp() → 2019-12-23 14:39:53.662522-05

to_timestamp （ double precision ）→ timestamp with time zone 将 Unix 纪元（自 1970-01-01 00:00:00+00 开始的秒数）转换为带有时区的 timestamp to_timestamp(1284352323) → 2010-09-13 04:32:03+00

除了这些函数外，还支持 SQL OVERLAPS 运算符：

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

当两个时间段（由时间段的开始和结束时间确定）重叠时，此表达式产生真值，不重叠时产生假值。结束时间可以指定为成对的日期、时间或时间戳；或为日期、时间或时间戳后跟一个间隔。当提供一对值时，可以先写入结束时间还是结束时间；OVERLAPS_自动将该对中的早些值作为开始时间。每个时间段被认为表示半开区间 _start ⇐ time < end，除非 start 和 end 相等，在这种情况下，它表示单个时间的瞬间。这表示两个时间段仅有一个结束时间重合就不会重叠。

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

当添加一个 interval 值（或从 timestamp 或 timestamp with time zone 值中减去一个 interval 值）到或从一个 interval 值中，这些 interval 值的月份、天数和微秒数将会被依次处理。首先，非零的月份数会根据月份数量，向前或向后推移或递减时间戳日期，除非这超过新月份的结束时间，则会使用该月份的最后一天。（例如，3月31日加1个月变成4月30日，但3月31日加2个月变成5月31日。）然后，天数会根据天数的数量，向前或向后推移或递减时间戳日期。在这两个步骤中，一天的时间会保持不变。最后，如果有一个非零的微秒数，它将会被相加或相减。当对一个 timestamp with time zone 值做算术时，该 timestamp with time zone 值位于识别夏时制时区中，这表示添加或减去（例如）interval '1 day'_并不一定会产生与添加或减去 _interval '24 hours' 相同的结果。比如，当会话时区设置为 America/Denver 时：

SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';
Result: 2005-04-03 12:00:00-06
SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';
Result: 2005-04-03 13:00:00-06

这是因为在时间 2005-04-03 02:00:00，由于时区 America/Denver 的夏令时发生了变化，导致跳过了一个小时。

请注意，由 age 返回的 months 域中可能有歧义，因为不同的月份有不同的天数。PostgreSQL 的方法在计算不完整月份时使用两个日期中较早的月份。例如，age('2004-06-01', '2004-04-30')_使用四月来生成 _1 mon 1 day，而使用五月会生成 1 mon 2 days，因为五月有31天，而四月只有30天。

日期和时间戳的减法也很复杂。一个概念上简单的方法执行减法是使用 EXTRACT(EPOCH FROM …​) 将个值转换为秒数，然后减去结果；这将生成两个值之间的 seconds 个数。这会根据每个月份的天数、时区变更和夏令时调整来进行调整。使用 “-” 运算符对日期或时间戳的值进行减法返回两个值之间的天数（24 小时）和小时数/分钟数/秒数，并进行相同的调整。age 函数返回年数、月数、天数和小时数/分钟数/秒数，执行逐域减法，然后调整负域值。以下查询展示了这些方法中的差异。示例结果使用 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.000000
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.9583333333333333
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. EXTRACT, date_part #

EXTRACT(field FROM source)

extract 函数从日期/时间值检索子域，例如年或小时。source 必须是类型为 timestamp、date、time 或 interval 的值表达式。（时间戳和时间可以带时区或不带时区。）field 是一个标识符或字符串，用于选择从源值中提取哪个域。并非所有域对于所有输入数据类型都是有效的；例如，无法从 date 中提取比一天小的域，而无法从 time 中提取一天或更长的域。extract 函数返回类型 numeric 的值。

以下字段名称有效：

  • century

    • 世纪；对于 interval 值，年份域除以 100

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
SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');
Result: 1
SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');
Result: -1
SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years');
Result: 20

  • day

    • 该月的第几天（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(&#8230;&#8203;, '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 以来的秒数（在此之前的对于时间戳为负数）；对于 date 和 timestamp 值，自 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.120000
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
Result: 982355920.120000
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800.000000

  • 您可以使用 to_timestamp 将一个纪元值转换回 timestamp with time zone：

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

  • 请注意，将 to_timestamp 应用于从 date 或 timestamp 值中提取的纪元值可能会产生误导性结果：结果实际上假定原始值已用 UTC 给出，但情况可能并非如此。

    • 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 日的星期一为开头，因此在 1 月初或 12 月末，ISO 年可能与公历年不同。更多信息，请参见 week 字段。

    • julian

  • Julian Date 对应于日期或时间戳。非本地午夜的时间戳导致分数值。更多信息见 Section B.7。

SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
Result: 2453737
SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
Result: 2453737.50000000000000000000

  • microseconds

    • 秒字段（包括小数部分），乘以 1 000 000；注意，这包括整秒。

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Result: 28500000

  • millennium

    • 千禧年；对于 interval 值，年字段除以 1000

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 3
SELECT EXTRACT(MILLENNIUM FROM INTERVAL '2001 years');
Result: 2

  • 20 世纪的年份在第二千禧年内。第三个千禧年开始于 2001 年 1 月 1 日。

    • milliseconds

  • 秒字段（包括小数部分），乘以 1000。注意，这包括整秒。

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
Result: 28500.000

  • minute

    • The minutes field (0–59)

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 38

  • month

    • 一年中月份的编号 (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

    • 秒字段（包括所有小数秒）

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 40.000000
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Result: 28.500000

  • timezone

    • 与 UTC 的时差，以秒为单位。正值对应于东时区的 UTC，负值对应于西时区的 UTC。（实际上，PostgreSQL 不使用 UTC，因为不支持闰秒。）

  • timezone_hour

    • 时区偏移的小时部分

  • timezone_minute

    • 时区偏移的分钟部分

  • week

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

    • 在 ISO 星期编号系统中，1 月初的日期可能属于前一年的第 52 周或第 53 周，12 月末的日期可能属于下一年的第一周。例如， 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

处理 interval 值时，extract 函数会产生匹配时间间隔输出函数所用解释的字段值。如果从非标准化的时间间隔表示开始，这可能会产生令人惊讶的结果，例如：

SELECT INTERVAL '80 minutes';
Result: 01:20:00
SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
Result: 20

Note

当输入值为 +/-Infinity 时，extract 为单调递增字段 (epoch、julian、year、isoyear、decade、century 和_millennium_) 返回 +/-Infinity。对于其他字段，返回 NULL。9.6 之前的 PostgreSQL 版本对于所有无限输入情况均返回零。

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

date_part 函数模仿了 SQL 标准函数_extract_的传统 Ingres 等效项：

date_part('field', source)

请注意，此处 field 参数需要为字符串值，而不是名称。date_part 的有效字段名称与 extract 的相同。出于历史原因，date_part 函数返回 double precision 类型的值。在某些用法中，这会导致精度损失。相反，建议使用 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 [, time_zone ])

source 是类型 timestamp、timestamp with time zone 或 interval 的值表达式。（类型 date 和 time 的值分别自动转换为 timestamp 或 interval。）field 选择将输入值截断到何种精度。返回值同样为类型 timestamp、timestamp with time zone 或 interval，并且它具有一些低于所选字段的所有字段，这些字段设置为 0（或对于 day 和 month 而言为 1）。

field 的有效值为：

当输入值类型为 timestamp with time zone 时，在特定时区执行截断；例如，截断至 day 产生该时区午夜的值。默认情况下，截断是在当前 TimeZone 设置的时区进行的，但可以提供可选的 time_zone 参数以指定其他时区。时区名称可以通过 Section 8.5.3 描述的任何方式指定。

处理 timestamp without time zone 或 interval 输入时不能指定时区。这些始终按面值接受。

示例（假设本地时区为 America/New_York）：

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
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Result: 2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00

9.9.3. date_bin #

函数 date_bin 将输入时间戳“分箱”到与指定原点对齐的指定间隔（stride）中。

date_bin(stride, source, origin)

source 是类型 timestamp 或 timestamp with time zone 的值表达式。（类型 date 的值自动转换为 timestamp。）stride 是类型 interval 的值表达式。返回值同样为类型 timestamp 或 timestamp with time zone，并且它标记了将_source_放置其中的分箱的开端。

示例：

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30

对于整单位（1 分钟、1 小时等），这会产生与类似 date_trunc 调用相同的结果，但不同之处在于，date_bin 可以截断为任意间隔。

stride 间隔必须大于 0，并且不能包含 month 或更大的单位。

9.9.4. AT TIME ZONE #

AT TIME ZONE 运算符将时间戳 without 时区转换为/从时间戳 with 时区及 time with time zone 值转换为不同的时区。 Table 9.34 显示了其变项。

Table 9.34. AT TIME ZONE Variants

Operator

Description

Example(s)

timestamp without time zone AT TIME ZONE zone → timestamp with time zone 转换给定的时间戳 without 时区为时间戳 with 时区，假设给定值处于指定时区中。 timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001-02-17 03:38:40+00

timestamp with time zone AT TIME ZONE zone → timestamp without time zone 将给定时间戳 with 时区转换为时间戳 without 时区，时间就像出现在该时区一样。 timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver' → 2001-02-16 18:38:40

time with time zone AT TIME ZONE zone → time with time zone 将给定时间 with 时区转换为新的时区。由于未提供日期，因此这将使用指定目标时区的当前有效 UTC 偏移量。 time with time zone '05:34:17-05' at time zone 'UTC' → 10:34:17+00

在这些表达式中，所需的时区 zone 可以指定为文本值（例如 'America/Los_Angeles'）或间隔（例如 INTERVAL '-08:00'）。在文本情况下，可以按照 Section 8.5.3 中描述的任何方式指定时区名称。间隔情况仅适用于与 UTC 固定的偏移量的时区，因此实际上并不常见。

示例（假设当前 TimeZone 设置为 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' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Result: 2001-02-16 05:38:40

第一个示例将时区添加到一个不包含时区的数值，并使用当前 TimeZone 设置显示该数值。第二个示例将带有时区值的时间戳移至指定时区，并返回不包含时区的数值。这样，便能够存储和显示不同于当前 TimeZone 设置的数值。第三个示例将东京时间转换为芝加哥时间。

timezone ( zone , timestamp )函数等同于符合 SQL 的结构 _timestamp AT TIME ZONE zone _.

9.9.5. Current Date/Time #

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

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

CURRENT_TIME 和 CURRENT_TIMESTAMP 提供带有时区的值；LOCALTIME 和 LOCALTIMESTAMP 提供不带时区的值。

CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME 和 LOCALTIMESTAMP 可选地采用精度参数，这样结果将在秒字段中舍入为很多有效数字。如果未采用精度参数，则将结果舍入为完全可用的精度。

举例：

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05
SELECT CURRENT_DATE;
Result: 2019-12-23
SELECT CURRENT_TIMESTAMP;
Result: 2019-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
Result: 2019-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
Result: 2019-12-23 14:39:53.662522

由于这些函数返回当前事务的开始时间，因此它们的值在事务期间不会发生变化。这被认为是一个特征：目的是允许单一事务对“当前”时间有一个一致的概念，以便在同一事务中的多个修改带有相同时间戳。

Note

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

PostgreSQL 还提供返回当前语句的开始时间以及函数调用瞬间实际的当前时间的函数。非 SQL 标准时间函数的完整列表如下：

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

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

所有日期/时间数据类型还接受特殊文字值 now，以指定当前日期和时间（同样，解释为事务开始时间）。因此，以下三个结果相同：

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';  -- but see tip below

Tip

在指定待稍后评估的值（例如表列的 DEFAULT 子句）时，不要使用第三种形式。系统将在解析常量后立即将 now 转换为 timestamp，以便在需要默认值时，将使用创建表的时间！前两种形式不会在使用默认值之前进行评估，因为它们是函数调用。因此，它们会给出按行的插入时间进行默认操作的所需行为。（另请参阅 Section 8.5.1.4。）

9.9.6. Delaying Execution #

以下函数可用于延迟服务器进程的执行：

pg_sleep ( double precision )
pg_sleep_for ( interval )
pg_sleep_until ( timestamp with time zone )

pg_sleep 使当前会话的进程休眠，直到给定的秒数过去。可以指定几分之一秒的延迟。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 或它的变体时会话不会持有比必须更多的锁。否则，其他会话必须等待你的休眠进程，减慢整个系统的速度。