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

Table 9-26 显示了 PostgreSQL 里可以用于处理日期/时间数值的函数,随后一节里描述了细节。 Table 9-25 演示了基本算术操作符 (+* 等等)的行为。 而与格式化相关的函数,可以参考Section 9.8。 你应该很熟悉来自 Section 8.5 的日期/时间数据类型的背景知识。

所有下面描述的函数和操作符接收的time或者timestamp输入实际上都来自两种可能: 一个是接收time with time zonetimestamp with time zone, 另外一种是time without time zone 或者 timestamp without time zone。 出于简化考虑,这些变种没有独立显示出来。还有,+* 操作符都是以可交换的操作符对方式存在的 (比如,date + integer 和 integer + date);我们只显示了这样的交换操作符对中的一个。

Table 9-25. 日期/时间操做符

操作符例子结果
+ 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'
- 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'

Table 9-26. 日期/时间函数

函数返回类型描述例子结果
age(timestamp, timestamp)interval减去参数,生成一个使用年、月的"符号化"的结果age('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
current_datedate今天的日期;见 Section 9.9.4   
current_timetime with time zone现在的时间;见 Section 9.9.4   
current_timestamptimestamp with time zone日期和时间;见 Section 9.9.4   
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_trunc(text, timestamp)timestamp截断成指定的精度;又见Section 9.9.2 date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00+00
extract(field from timestamp)double precision获取子域;又见 Section 9.9.1 extract(hour from timestamp '2001-02-16 20:38:40')20
extract(field from interval)double precision获取子域;又见 Section 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
localtimetime今日的时间;见 Section 9.9.4   
localtimestamptimestamp日期和时间;见 Section 9.9.4   
now()timestamp with time zone当前的日期和时间(等效于 current_timestamp);见Section 9.9.4   
timeofday()text当前日期和时间;见Section 9.9.4   

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

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

这个表达式在两个时间域(用它们的终点定义)重叠的时候生成真值。 终点可以以一对日期,时间,或者时间戳来声明;或者是一个后面跟着一个时间间隔的 日期,时间,时间戳。

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. EXTRACTdate_part

EXTRACT (field from source)

extract 函数从日期/时间数值里抽取 子域,比如年或者小时等。source 必须是一个类型 timestamptime,或者 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 AD 开始, 尽管那时候人们还不知道这是第一个世纪。这个定义适用于所有使用格里高利历法的国家。 没有 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 函数的不同。

doy

一年的第几天(1 -365/366) (仅用于 timestamp)

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

对于 datetimestamp 数值而言, 是自 1970-01-01 00:00:00 以来的秒数(结果可能是负数。); 对于 interval 数值而言,它是时间间隔的总秒数。

SELECT EXTRACT(EPOCH from TIMESTAMP '2001-02-16 20:38:40');
Result: 982352320

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

20世纪(19xx年)里面的年份在第二个千年里。第三个千年从 2001 年一月一日开始。

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 [1])

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), 一年的第一周包含该年的一月四日。(ISO-8601的周从星期一开始。) 换句话说,一年的第一个星期四在第一周。(只用于 timestamp 数值)。

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

extract 函数主要的用途是做运算用。 对于用于显示的日期/时间数值格式化,参阅 Section 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 的值表达式(类型 datetime 的数值都分别自动转换成timestamp或者interval)。 用 field 选择对该时间戳数值 选用什么样的精度进行截断)。 返回的数值是 timestamp 类型或者interval,所有小于选定的 精度的域都设置为零(或者一,如果是日期和月份域的话)。

field 的有效数值是∶

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

例子:

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

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

9.9.3. AT TIME ZONE

AT TIME ZONE 构造允许把时间戳转换成不同的 时区。Table 9-27 显示了其变体。

Table 9-27. AT TIME ZONE变体

表达式返回类型描述
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')。 在文本的情况下,可用的时区名字在 Table B-4 里显示。 (可能支持更通用的 Table B-6 会更好些,不过目前这些还没有实现。)

例子(假设本地时区是 PST8PDT):

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

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 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_TIMECURRENT_TIMESTAMP 带有时区值; LOCALTIMELOCALTIMESTAMP 的数值没有时区值。

CURRENT_TIMECURRENT_TIMESTAMPLOCALTIMELOCALTIMESTAMP 可以有选择地给予一个精度参数, 该精度导致结果的秒数域园整为指定小数位。如果没有精度参数, 将给予所能得到的全部精度。

注意: PostgreSQL 7.2 之前没有实现精度参数, 结果总是给出整数的秒。

一些例子:

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

函数 now() 是传统的 PostgreSQLCURRENT_TIMESTAMP 的等效物。

还有一个 timeofday() 函数,由于历史原因, 它返回一个字串,而不是 timestamp 值∶

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

还有一件事提醒大家,那就是 CURRENT_TIMESTAMP 和相关的函数把时间当做当前事务的开始返回;在事务运行的时候, 它们的数值并不改变。 我们认为这是一个特性:目的是为了允许一个事务在"当前" 时间上有连贯的概念,这样在同一个事务离得多个修改可以有同样的时间戳。 但 timeofday() 返回当前的实际时间,并且随着事务的处理会前进。

注意: 许多其它数据库系统更频繁地更新这些数值。

所有日期/时间类型还接受特殊的文本值 now, 用于声明当前的日期和时间。因此,下面三个都返回相同的结果∶

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

提示: 在创建表声明一个DEFAULT值的时候你是不会想用第三种形式的。 因为系统将在分析这个常量的时候把 now 转换成 一个 timestamp,因此在需要缺省值的时候, 就会使用创建表的时间! 而头两种形式要到实际使用缺省值的时候才计算, 因为它们是函数调用。因此它们可以给出插入时间行的时候 需要的缺省行为。

Notes

[1]

如果操作系统实现了润秒, 那么上限是 60