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

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 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
历史版本PostgreSQL 8.0 已于 2010 年 10 月结束社区维护，本页译文保留供仍在使用旧版本的读者参考。新系统请看当前版本。
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.26展示了可用于处理日期/时间值的函数，其细节在随后的小节中描述。表 9.25演示了基本算术操作符 （+、*等）的行为。 而与格式化相关的函数，可以参考第 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.25. 日期/时间操作符

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

表 9.26. 日期/时间函数

Function Return Type 描述 示例 Result
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
current_date date Today's date; see 第 9.9.4 节    
current_time time with time zone Time of day; see 第 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(timestamp) boolean 测试是否为有限时间戳（不等于无穷） isfinite(timestamp '2001-02-16 21:28:30') true
isfinite(interval) boolean 测试时间间隔是否有限 isfinite(interval '4 hours') true
localtime time Time of day; see 第 9.9.4 节    
localtimestamp timestamp 日期和时间；见第 9.9.4 节    
now() timestamp with time zone 当前的日期和时间（等价于 current_timestamp）；见第 9.9.4 节    
timeofday() text Current date and time; see 第 9.9.4 节    

除了这些函数以外，还支持 SQL 操作符OVERLAPS：

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

当两个时间段（由其端点定义）重叠时，该表达式产生真；不重叠时产生假。端点可以指定为一对日期、时间或时间戳；也可以是一个日期、时间或时间戳后跟一个 interval。

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

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

第一个世纪从公元 0001-01-01 00:00:00 开始，尽管当时的人并不知道这一点。这个定义适用于所有使用格里高利历的国家。没有世纪编号 0，你从 -1 世纪直接到 1 世纪。 如果对此有异议，请把你的意见寄到：罗马，圣彼得大教堂，教皇收。

PostgreSQL 8.0 之前的版本不遵循通常的世纪编号，而只是返回年份数字除以 100 的结果。

day

（月中的）日字段（1 - 31）

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

年份字段除以 10

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

一周中的日（0 - 6；周日是 0）（仅用于timestamp值）

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

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

doy

一年中的第几天（1 - 365/366）（仅用于timestamp值）

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

对date和timestamp值，是自 1970-01-01 00:00:00 UTC 以来的秒数（可以为负）； 对interval值，是该 interval 中的总秒数

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
Result: 982384720

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

下面说明如何把 epoch 值转换回时间戳：

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

小时域（0 - 23）

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

秒字段（包含小数部分）乘以 1 000 000；注意这包含完整的秒

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

千年域

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

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

PostgreSQL 8.0 之前的版本不遵循通常的千年编号，而只是返回年份数字除以 1000 的结果。

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）（仅用于timestamp值）

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

秒字段（包含小数部分）（0 - 59[5]）

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 以西的时区。

timezone_hour

时区偏移的小时部分

timezone_minute

时区偏移的分钟部分

week

该日期在一年中的周号。按照定义（ISO 8601），一年的第 1 周包含该年的 1 月 4 日。（ISO-8601 的周从星期一开始。）换句话说，一年的第一个星期四属于该年的第 1 周。（仅用于timestamp值）

因此，1 月上旬的日期有可能属于上一年的第 52 或 53 周。例如，2005-01-01属于 2004 年的第 53 周，而2006-01-01属于 2005 年的第 52 周。

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

年份字段。请记住没有公元 0 年，因此从公元（AD）年份减去公元前（BC）年份时应小心。

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 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');
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是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.27展示了它的各种变体。

表 9.27. AT TIME ZONE Variants

Expression Return Type 描述
timestamp without time zone AT TIME ZONE zone timestamp with time zone 把给定时区中的本地时间转换为 UTC
timestamp with time zone AT TIME ZONE zone timestamp without time zone 把 UTC 转换为给定时区中的本地时间
time with time zone AT TIME ZONE zone time with time zone 跨时区转换本地时间

在这些表达式里，所需的时区zone可以指定为文本字符串（例如'PST'），也可以指定为一个间隔（例如INTERVAL '-08:00'）。在文本情况下，可用的时区名是表 B.4中所示的那些。（支持表 B.6中所示的更通用的名称会很有用，但尚未实现。）

示例（假定本地时区为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）以产生一个 UTC 时间戳，然后旋转到 PST（UTC-8）显示。第二个示例取一个以 EST（UTC-5）指定的时间戳，把它转换成 MST（UTC-7）的本地时间。

函数timezone(zone, timestamp)等效于符合 SQL 标准的结构timestamp AT TIME ZONE zone。

9.9.4. 当前日期/时间 #

可以使用下列函数获得当前日期和/或时间：

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可以有选择地接受一个精度参数，该精度会使结果的秒字段舍入到指定的小数位数。如果没有精度参数，结果将给出可用的全部精度。

注意

在PostgreSQL 7.2 之前，精度参数是没有实现的，结果总是以整数秒给出。

下面是一些示例：

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

函数now()是PostgreSQL中与CURRENT_TIMESTAMP等价的传统函数。

还有函数timeofday()，出于历史原因，它返回一个text字符串而不是timestamp值：

SELECT timeofday();
结果: Sat Feb 17 19:07:32.000126 2001 EST

重要的是要知道CURRENT_TIMESTAMP及相关函数返回的是当前事务的开始时间；它们的值在事务运行期间不会改变。这被认为是一个特性：目的是为了允许一个事务在“当前”时间上有一致的概念，这样在同一个事务里的多个修改可以保持同样的时间戳。timeofday()返回墙上时钟时间，在事务运行期间会推进。

注意

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

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

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';

提示

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


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

上一页  上一级  下一页
9.8. 数据类型格式化函数  起始页  9.10. 几何函数和操作符

提交更正

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