PostgreSQL 时间/日期函数和操作符
- 9.9.1.
EXTRACT,date_part - 9.9.2.
date_trunc - 9.9.3.
AT TIME ZONE - 9.9.4. 当前日期/时间
- 9.9.5. 延时执行
表 9.32展示了可用于处理日期/时间值的函数，其细节在随后的小节中描述。表 9.31演示了基本算术操作符
（+、*等）的行为。 而与格式化相关的函数，可以参考第 9.8 节。你应该很熟悉第 8.5 节中的日期/时间数据类型的背景知识。
所有下文描述的接受time或timestamp输入的函数和操作符实际上都有两种变体： 一种接收time with time zone或timestamp with time zone， 另外一种接受time without time zone或者
timestamp without time zone。为了简化，这些变种没有被独立地展示。此外，+和*操作符都是可交换的操作符对（例如，date + integer 和 integer + date）；我们只显示其中一个。
表 9.31. 日期/时间操作符
|
操作符 描述 例子 |
|---|
|
给日期加上天数 |
|
为日期添加时间间隔 |
|
在日期中添加一天中的时间 |
|
添加时间间隔 |
|
在时间戳中添加一个时间间隔 |
|
为时间添加时间间隔 |
|
取否一个时间间隔 |
|
减去日期，生成经过的天数 |
|
从日期中减去天数 |
|
从日期中减去时间间隔 |
|
减去时间 |
|
从时间中减去时间间隔 |
|
从时间戳中减去时间间隔 |
|
减去时间间隔 |
|
减去时间戳（将24小时间隔转换为天，类似于 |
|
将时间间隔乘以数量 |
|
用时间间隔除以数量 |
表 9.32. 日期/时间函数
(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');
结果：true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
结果：false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30', DATE '2001-10-31');
结果：false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30', DATE '2001-10-31');
结果：true
当把一个interval值添加到timestamp with time zone上（或从中减去）时， 天的部分会按照指定的天数增加或减少timestamp with time zone的日期，保持一天中相同的时间。 对于横跨夏令时的变化（当会话的时区被设置为可识别DST的时区时），这意味着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
发生此情况是因为夏令时在America/Denver时区的2005-04-03 02:00:00的时间发生更改而跳过了一个小时。
注意age返回的月数域可能有歧义，因为不同的月份有不同的天数。 PostgreSQL的方法是当计算部分月数时，采用两个日期中较早的月。例如：age('2004-06-01', '2004-04-30')使用4月份得到1 mon 1 day，而用5月分时会得到
1 mon 2 days，因为5月有31天，而4月只有30天。
日期和时间戳的减法也可能会很复杂。执行减法的一种概念上很简单的方法是，使用 EXTRACT(EPOCH FROM ...)把每个值都转换成秒数，然后执行减法， 这样会得到两个值之间的秒数。这种方法将会适应每个月中天数、 时区改变和夏令时调整。使用“-”操作符的日期或时间
戳减法会返回值之间的天数（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
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. EXTRACT, date_part
EXTRACT(field FROM source)
extract函数从日期/时间值中抽取子域，例如年或者小时等。source必须是一个类型 timestamp、time或interval的值表达式（类型为date的表达式将被造型为
timestamp，并且因此也可以被同样使用）。field是一个标识符或者字符串，它指定从源值中抽取的域。extract函数返回类型为double precision的值。 下列值是有效的域名字∶
century-
世纪
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); 结果：
20SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：21第一个世纪从 0001-01-01 00:00:00 AD 开始， 尽管那时候人们还不知道这是第一个世纪。这个定义适用于所有使用格里高利历法的国家。其中没有 0 世纪，我们直接从公元前 1 世纪到公元 1 世纪。 如果你认为这个不合理，那么请把抱怨发给：罗马圣彼得教堂，梵蒂冈，教皇收。
day-
对于
timestamp值，是（月份）里的日域（1–31）；对于interval值，是日数SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
16SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute'); 结果：40 decade-
年份域除以10
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
200 dow-
一周中的日，从周日（
0）到周六（6）SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
5请注意，
extract的一周中的日和to_char(..., 'D')函数不同。 doy-
一年的第几天（1–365/366）
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
47 epoch-
对于
timestamp with time zone值， 是自 1970-01-01 00:00:00 UTC 以来的秒数（结果可能是负数）； 对于dateandtimestamp值，是自本地时间 1970-01-01 00:00:00 以来的描述；对于interval值，它是时间间隔的总秒数。SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08'); 结果：
982384720.12SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); 结果：442800不能用
to_timestamp把一个 epoch 值转换回成时间戳：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'); 结果：
20 isodow-
一周中的日，从周一（
1）到周日（7）SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40'); 结果：
7除了周日，这和
dow相同。这符合ISO 8601 中一周中的日的编号。 isoyear-
日期所落在的ISO 8601 周编号的年（不适用于间隔）
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01'); 结果：
2005SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); 结果：2006每一个ISO 8601 周编号的年都开始于包含1月4日的那一周的周一，在早的1月或迟的12月中ISO年可能和格里高利年不同。更多信息见
week域。这个域不能用于 PostgreSQL 8.3之前的版本。
microseconds-
秒域，包括小数部分，乘以 1,000,000。请注意它包括全部的秒
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5'); 结果：
28500000 millennium-
千年
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
319xx的年份在第二个千年里。第三个千年从 2001 年 1 月 1 日开始。
milliseconds-
秒域，包括小数部分，乘以 1000。请注意它包括完整的秒。
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); 结果：
28500 minute-
分钟域（0–59）
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
38 month-
对于
timestamp值，它是一年里的月份数（1–12）； 对于interval值，它是月的数目，然后对 12 取模（0–11）SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
2SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months'); 结果：3SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months'); 结果：1 quarter-
该天所在的该年的季度（1–4）
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
1 second-
秒字段，包括任何小数秒。
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
40SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); 结果：28.5 timezone-
与 UTC 的时区偏移，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区（从技术上来看，PostgreSQL不使用 UTC，因为其中不处理闰秒）。
timezone_hour-
时区偏移的小时部分。
timezone_minute-
时区偏移的分钟部分。
week-
该天在所在的ISO 8601 周编号的年份里是第几周。根据定义， 一年的第一周包含该年的 1月 4 日并且 ISO 周从星期一开始。换句话说，一年的第一个星期四在第一周。
在 ISO 周编号系统中，早的 1 月的日期可能位于前一年的第五十二或者第五十三周，而迟的 12 月的日期可能位于下一年的第一周。例如，
2005-01-01位于 2004 年的第五十三周，并且2006-01-01位于 2005 年的第五十二周，而2012-12-31位于 2013 年的第一周。我们推荐把isoyear域和week一起使用来得到一致的结果。SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
7 year-
年份域。要记住这里没有
0 AD，所以从AD年里抽取BC年应该小心处理。SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); 结果：
2001
注意
当输入值为 +/-Infinity 时，extract对于单调增的域（epoch、julian、year、isoyear、decade、century以及
millennium）返回 +/-Infinity。对于其他域返回 NULL。PostgreSQL 9.6 之前的版本对所有输入无穷的情况都返回零。
extract函数主要的用途是做计算性处理。对于用于显示的日期/时间值格式化，参阅第 9.8 节。
在传统的Ingres上建模的date_part函数等价于SQL标准函数extract：
date_part('field', source)
请注意这里的field参数必须是一个串值，而不是一个名字。有效的date_part域名 和extract相同。
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
结果：16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
结果：4
9.9.2. date_trunc
date_trunc函数在概念上和用于数字的trunc函数类似。
date_trunc(field, source [, time_zone ])
source是类型timestamp或interval的值表达式（类型date和 time的值都分别被自动转换成timestamp, timestamp with time zone,或者
interval）。field选择对输入值选用什么样的精度进行截断。返回的值是timestamp, timestamp with time zone,类型或者所有小于选定的 精度的域都设置为零（或者一，对于日期和月份）的interval。
field的有效值是∶
microseconds
|
milliseconds
|
second
|
minute
|
hour
|
day
|
week
|
month
|
quarter
|
year
|
decade
|
century
|
millennium
|
当输入值的类型为timestamp with time zone时。截断是针对特定时区进行的。 例如，截断为day，产生的值是 是该区域的午夜。 默认情况下，截断是在以下方面进行的 到当前的TimeZone设置，但在当前的 可以提供可选的time_zone参数。以指定不同的时区。
可以指定时区名称 第 8.5.3 节中描述的任何一种方式。
当处理timestamp without time zone 或interval输入时，不能指定时区。 这些总是按表面值来处理。
示例 (假设当地时区为 America/New_York):
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
结果：2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
结果：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. AT TIME ZONE
AT TIME ZONE把时间戳without时区转换成时间戳with时区或者反过来，并且把time with time zone值转换成不同的时区。表 9.33展示了它的变体。
表 9.33. AT TIME ZONE变体
|
操作符 描述 例子 |
|---|
|
将给定的时间戳without时区转换为时间戳with时区，假设给定的值在指定的时区内。 |
|
将给定的时间戳with时区转换为时间戳without时区，因为时间将出现在该时区中 |
|
将给定的时间with时区转换为新的时区。由于没有提供日期，这将使用指定目的区域的当前活动UTC偏移量。 |
在这些表达式里，我们需要的时区zone可以指定为文本值（例如，'America/Los_Angeles'）或者一个间隔 （例如，INTERVAL '-08:00'）。 在文本情况下，可用的时区名字可以用第 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)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_TIME和CURRENT_TIMESTAMP传递带有时区的值；LOCALTIME和LOCALTIMESTAMP传递的值不带时区。
CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME和 LOCALTIMESTAMP可以有选择地接受一个精度参数， 该精度导致结果的秒域被园整为指定小数位。如果没有精度参数，结果将被给予所能得到的全部精度。
一些例子：
SELECT CURRENT_TIME;
结果: 14:39:53.662522-05
SELECT CURRENT_DATE;
结果: 2019-12-23
SELECT CURRENT_TIMESTAMP;
结果: 2019-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
结果: 2019-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
结果: 2019-12-23 14:39:53.662522
因为这些函数全部都按照当前事务的开始时刻返回结果，所以它们的值在事务运行的整个期间内都不改变。 我们认为这是一个特性：目的是为了允许一个事务在“当前”时间上有一致的概念， 这样在同一个事务里的多个修改可以保持同样的时间戳。
注意
许多其它数据库系统可能会更频繁地推进这些值。
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()相似，
timeofday()也返回真实的当前时间，但是它的结果是一个格式化的text串，而不是timestamp with time zone值。now()是PostgreSQL的一个传统，等效于transaction_timestamp()。
所有日期/时间类型还接受特殊的文字值now，用于指定当前的日期和时间（重申，被解释为当前事务的开始时刻）。 因此，下面三个都返回相同的结果：
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now'; -- 但请参阅下面的提示
提示
当指定以后要计算的值时，不要使用第三种形式，例如在表列的DEFAULT子句中。 系统将在分析这个常量的时候把now转换为一个timestamp， 这样需要默认值时就会得到创建表的时间！而前两种形式要到实际使用缺省值的时候才被计算， 因为它们是函数调用。因此它们可以给出每次插入行的时刻。 （参见 第 8.5.1.4 节。）
9.9.5. 延时执行
下面的这些函数可以用于让服务器进程延时执行：
pg_sleep ( double precision )
pg_sleep_for ( interval )
pg_sleep_until ( timestamp with time zone )
pg_sleep使当前会话的进程休眠，直到过去给定的秒数。可以指定几分之一秒的延迟。 pg_sleep_for是一个方便的函数，允许将睡眠时间指定为时间间隔。 pg_sleep_until是一个方便的函数，用于需要特定的唤醒时间。例如：
SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');
注意
有效的休眠时间间隔精度是平台相关的，通常 0.01 秒是通用值。休眠延迟将至少持续指 定的时长， 也有可能由于服务器负荷而比指定的时间长。特别地， pg_sleep_until并不保证能刚好在指定的时刻被唤醒，但它不会 在比指定时刻早的时候醒来。
警告
请确保在调用pg_sleep或者其变体时，你的会话没有持有不必要 的锁。否则其它会话可能必须等待你的休眠会话，因而减慢整个系统速度。