↑↓ 选择 ↵ 打开 ⌫ 改范围 完整检索页

pgsql.cc 提供对 postgresql.org 官网内容的中文翻译，由 Pigsty 团队维护。

受支持版本： 当前版本 (18) / 17 / 16 / 15 / 14
测试与开发版本： 19 / devel
不受支持的版本： 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0
历史版本PostgreSQL 9.2 已于 2017 年 11 月结束社区维护，本页译文保留供仍在使用旧版本的读者参考。新系统请看当前版本。
9.9. 日期/时间函数和操作符
上一页  上一级 第 9 章 函数和操作符 起始页  下一页

9.9. 日期/时间函数和操作符 #

9.9.1. EXTRACT, date_part
9.9.2. date_trunc
9.9.3. AT TIME ZONE
9.9.4. 当前日期/时间
9.9.5. 延时执行

表 9.28展示了可用于处理日期/时间值的函数，其细节在随后的小节中描述。表 9.27演示了基本算术操作符 （+、*等）的行为。 而与格式化相关的函数，可以参考第 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.27. 日期/时间操作符

操作符 示例 结果
+ 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.28. 日期/时间函数

函数 返回类型 描述 示例 结果
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') 43 years 8 mons 3 days
clock_timestamp() timestamp with time zone 当前日期和时间（在语句执行期间变化）；参见第 9.9.4 节    
current_date date 当前日期；参见 第 9.9.4 节    
current_time time with time zone 一天中的当前时刻；参见第 9.9.4 节    
current_timestamp timestamp with time zone 当前日期和时间（当前事务开始时）；参见第 9.9.4 节    
date_part(text, timestamp) double precision 获取子字段（等价于 extract）；参见第 9.9.1 节 date_part('hour', timestamp '2001-02-16 20:38:40') 20
date_part(text, interval) double precision 获取子字段（等价于 extract）；参见第 9.9.1 节 date_part('month', interval '2 years 3 months') 3
date_trunc(text, timestamp) timestamp 截断到指定精度；另见第 9.9.2 节 date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00
extract(field from timestamp) double precision 获取子字段；参见第 9.9.1 节 extract(hour from timestamp '2001-02-16 20:38:40') 20
extract(field from interval) double precision 获取子字段；参见第 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 '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_days 和 justify_hours调整时间间隔，并额外调整符号 justify_interval(interval '1 mon -1 hour') 29 days 23:00:00
localtime time 一天中的当前时刻；参见第 9.9.4 节    
localtimestamp timestamp 当前日期和时间（当前事务开始时）；参见第 9.9.4 节    
now() timestamp with time zone 当前日期和时间（当前事务开始时）；参见第 9.9.4 节    
statement_timestamp() timestamp with time zone 当前日期和时间（当前语句开始时）；参见 第 9.9.4 节    
timeofday() text 当前的日期和时间 （类似 clock_timestamp, 但是采用 text 字符串）；参见 第 9.9.4 节    
transaction_timestamp() timestamp with time zone 当前日期和时间（当前事务开始时）；参见第 9.9.4 节    

除了这些函数以外，还支持 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');
结果：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值上（或从中减去一个interval值）时，天数部分会按指定的天数推进或退回timestamp with time zone的日期。当跨越夏令时变化时（会话时区设置为识别夏令时的时区），这意味着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'，而向同一个初始timestamp with time zone加上interval '24 hours'会得到timestamp with time zone '2005-04-03 13:00-06'，这是因为在时区CST7CDT中，夏令时变化发生在2005-04-03 02:00。

注意，age返回的months字段可能存在歧义，因为不同月份的天数不同。PostgreSQL在计算不足整月的部分时，会采用两个日期中较早的那个日期所在的月份。例如：age('2004-06-01', '2004-04-30')使用 4 月得到1 mon 1 day，而如果使用 5 月则会得到1 mon 2 days，因为 5 月有 31 天，而 4 月只有 30 天。

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');
结果：20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：21

第一个世纪始于公元 0001-01-01 00:00:00，尽管当时的人们并不知道。这一定义适用于所有采用格里高利历的国家。世纪编号没有 0，而是从 -1 世纪直接跳到 1 世纪。如果你对此有异议，请向梵蒂冈罗马圣彼得大教堂的教皇投诉。

PostgreSQL 8.0 之前的版本不遵循传统的世纪编号方式，而是直接返回年份字段除以 100 的结果。

day

对于 timestamp 值，表示月份中的日期字段（1 - 31）；对于 interval 值，表示天数

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：16
SELECT 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 以来的秒数（可以为负）；对于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');
结果：982384720.12

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
结果：442800

下面介绍如何把一个 epoch 值转换回时间戳：

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';

（to_timestamp函数封装了上述转换。）

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');
结果：2005
SELECT 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');
结果：3

1900 年代的年份属于第二个千年。第三个千年始于 2001 年 1 月 1 日。

PostgreSQL 8.0 之前的版本不遵循传统的千年 编号方式，而是直接返回年份字段除以 1000 的结果。

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');
结果：2
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
结果：3
SELECT 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

秒字段，包括小数部分（0 - 59[6]）

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
结果：40
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
结果：28.5
timezone

与UTC的时区偏移量，以秒为单位。正值对应于UTC东部的时区，负值对应于UTC西部的时区。 （从技术上讲，PostgreSQL使用 UT1 而不是 UTC，因为不处理闰秒。）

timezone_hour

时区偏移的小时部分

timezone_minute

时区偏移的分钟部分

week

一年中按ISO 8601 周编号体系计算的周序号。根据定义，ISO 周从周一开始，一年的第一周包含该年的 1 月 4 日。换句话说，一年的第一个星期四在该年的第 1 周。

在 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');
结果：7
year

年份字段。请记住，没有0 AD，所以把BC年份从AD年份中减去时需要小心。

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
结果：2001

extract函数主要的用途是做计算性处理。对于用于显示的日期/时间值格式化，参阅第 9.8 节。

date_part函数仿照传统的Ingres实现，后者对应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)

source是timestamp或interval类型的值表达式。（类型为date和time的值会自动转换为timestamp或interval，分别对应这两种输入类型。）field选择输入值的截断精度。返回值的类型是timestamp或interval，所有小于所选精度的字段都设为零（日和月则设为一）。

field的有效值是：

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

示例：

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

9.9.3. AT TIME ZONE #

AT TIME ZONE 结构允许把时间戳转换到不同的时区。表 9.29展示了它的各种变体。

表 9.29. AT TIME ZONE 变体

表达式 返回类型 描述
timestamp without time zone AT TIME ZONE zone timestamp with time zone 将给定的不带时区时间戳视为指定时区中的时间
timestamp with time zone AT TIME ZONE zone timestamp without time zone 将给定的带时区时间戳转换为新时区的时间，结果不带时区标识
time with time zone AT TIME ZONE zone time with time zone 将给定的带时区时间转换为新时区的时间

在这些表达式里，所需的时区 zone 可以指定为文本值（例如 'PST'），也可以指定为一个间隔值（例如 INTERVAL '-08:00'）。 在文本情况下，时区名称可以按 第 8.5.3 节 中描述的任意方式指定。

示例（假设本地时区为PST8PDT）：

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
结果：2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
结果：2001-02-16 18:38:40

第一个示例取一个不带时区的时间戳，把它解释为 MST 时间（UTC-7），然后转换为 PST（UTC-8）用于显示。第二个示例取一个以 EST（UTC-5）指定的时间戳，把它转换为 MST（UTC-7）的本地时间。

函数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_TIME和CURRENT_TIMESTAMP返回带时区的值；LOCALTIME和LOCALTIMESTAMP返回不带时区的值。

CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME和 LOCALTIMESTAMP可以有选择地接受一个精度参数，该精度会使结果的秒字段舍入到指定的小数位数。如果没有精度参数，结果将给出可用的全部精度。

下面是一些示例：

SELECT CURRENT_TIME;
结果: 14:39:53.662522-05
SELECT CURRENT_DATE;
结果: 2001-12-23
SELECT CURRENT_TIMESTAMP;
结果: 2001-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
结果: 2001-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
结果: 2001-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()相似，它也返回真实的当前时间，但是它的结果是一个格式化的text串，而不是timestamp with time zone值。now()是PostgreSQL中与transaction_timestamp()等价的传统函数。

所有日期/时间数据类型也都接受特殊字面值now来指定当前日期和时间（同样解释为事务开始时间）。因此，下面三种写法都返回相同的结果：

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

提示

在创建表时指定DEFAULT子句的情况下，你不希望使用第三种形式。 系统将在分析这个常量的时候把now转换为一个timestamp， 这样需要默认值时就会得到创建表的时间！而前两种形式要到实际使用默认值的时候才被计算， 因为它们是函数调用。因此它们可以给出每次插入行的时刻。

9.9.5. 延时执行 #

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

pg_sleep(seconds)

pg_sleep使当前会话的进程休眠seconds秒。seconds的类型为double precision，因此可以指定带小数部分的秒数作为延迟时间。例如：

SELECT pg_sleep(1.5);

注意

有效的休眠时间间隔精度是平台相关的，通常 0.01 秒是通用值。休眠延迟将至少持续指 定的时长， 也有可能由于服务器负荷而比指定的时间长。特别地， 休眠并不保证能刚好在指定的时刻结束，但它不会 在比指定时刻早的时候醒来。

警告

请确保在调用pg_sleep或者其变体时，你的会话没有持有不必要 的锁。否则其它会话可能必须等待你的休眠会话，因而减慢整个系统速度。


[6] 如果操作系统实现了闰秒，则为 60

上一页  上一级  下一页
9.8. 数据类型格式化函数  起始页  9.10. 枚举支持函数

提交更正

译文有误、术语不当或页面显示问题，请到译文仓库 pgsty/pgdoc 报告译文问题。 英文原文本身的问题，请在当前版本的对应页面向上游反馈；上游不再修订已结束维护的版本。