Table 9-26 显示了 PostgreSQL 里可以用于处理日期/时间数值的函数,随后一节里描述了细节。 Table 9-25 演示了基本算术操作符 (+,* 等等)的行为。 而与格式化相关的函数,可以参考Section 9.7。 你应该很熟悉来自 Section 8.5 的日期/时间数据类型的背景知识。
所有下面描述的函数和操作符接收的time或者timestamp输入实际上都来自两种 可能:一个是接收time with time zone 或 timestamp with time zone, 另外一种是time without time zone 或者 timestamp without time zone。 出于简化考虑,这些变种没有独立显示出来。
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' |
+ | time '03:00' + date '2001-09-28' | 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 '3 hours' + time '01:00' | 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' |
- | interval '2 hours' - time '05:00' | time '03:00' |
- | timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' | interval '1 day 15:00' |
* | double precision '3.5' * interval '1 hour' | interval '03:30' |
* | 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) | interval | 从现在减去得到的数值 | age(timestamp '1957-06-13') | 43 years 8 mons 3 days |
age(timestamp, timestamp) | interval | 减去参数 | age('2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days |
current_date | date | 今天的日期;见 Section 9.8.4 | ||
current_time | time with time zone | 现在的时间;见 Section 9.8.4 | ||
current_timestamp | timestamp with time zone | 日期和时间;见下文 Section 9.8.4 | ||
date_part(text, timestamp) | double precision | 获取子域(等效于 extract);又见 | date_part('hour', timestamp '2001-02-16 20:38:40') | 20 |
date_part(text, interval) | double precision | 获取子域(等效于 extract);又见 | date_part('month', interval '2 years 3 months') | 3 |
date_trunc(text, timestamp) | timestamp | 截断成指定的精度;又见Section 9.8.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.8.1 | extract(hour from timestamp '2001-02-16 20:38:40') | 20 |
extract(field from interval) | double precision | 获取子域;又见 Section 9.8.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 | 今日的时间;见 Section 9.8.4 | ||
localtimestamp | timestamp | 日期和时间;见 Section 9.8.4 | ||
now() | timestamp with time zone | 当前的日期和时间(等效于 current_timestamp);见Section 9.8.4 | ||
timeofday() | text | 当前日期和时间;见Section 9.8.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
EXTRACT (field from source)
extract 函数从日期/时间数值里抽取 子域,比如年或者小时等。source 是一个值表达式,可以计算出类型 timestamp 或者 interval。(类型为 date 或者 time 的表达式将转换为 timestamp 然后再处理。)field 是一个标识符 或者字串,它指定从源数据中抽取的数域。extract 函数返回类型为double precision 的数值。 下列数值是有效数据域的名字∶
年份域除以100
SELECT EXTRACT(CENTURY from TIMESTAMP '2001-02-16 20:38:40'); Result: 20
请注意,世纪数据域只是简单的年份域除以100,而不是传统的那样把 大多数19xx年放到二十世纪。
(月分)里的日期域(1-31)
SELECT EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40'); Result: 16
年份域除以10
SELECT EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40'); Result: 200
每周的星期号(0 - 6;星期天是 0) (仅用于 timestamp)
SELECT EXTRACT(DOW from TIMESTAMP '2001-02-16 20:38:40'); Result: 5
一年的第几天(1 -365/366) (仅用于 timestamp)
SELECT EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40'); Result: 47
对于 date 和 timestamp 数值而言, 是自 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
小时域 (0 - 23)
SELECT EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40'); Result: 20
秒域,包括小数部分,乘以 1,000,000。请注意它包括全部的秒。
SELECT EXTRACT(MICROSECONDS from TIME '17:12:28.5'); Result: 28500000
年域除以 1000
SELECT EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40'); Result: 2
请注意千年域只是简单的用 1000 除年域,而不是传统那样定义的 19xx 年是第二个千年。
秒域,包括小数部分,乘以 1000。请注意它包括完整的秒。
SELECT EXTRACT(MILLISECONDS from TIME '17:12:28.5'); Result: 28500
分钟域 (0 - 59)
SELECT EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40'); Result: 38
对于 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
该天所在的该年的季度(1 - 4)(仅用于 timestamp)
SELECT EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40'); Result: 1
秒域,包括小数部分 (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
与 UTC 的时区偏移,以秒记。正数对应 UTC 东边的时区,负数对应 UTC 西边的时区。
时区偏移的小时部分。
时区偏移的分钟部分。
该天在所在的年份里 是第几周。根据定义 (ISO 8601),一年的 第一周包含该年的一月四日。(ISO-8601的周从星期一开始。) 换句话说,一年的第一个星期四在第一周。(只用于 timestamp 数值)。
SELECT EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40'); Result: 7
年份域
SELECT EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40'); Result: 2001
extract 函数主要的用途是做运算用。 对于用于显示的日期/时间数值格式化,参阅 Section 9.7。
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
date_trunc 函数在概念上和用于 数字的 trunc 函数类似。
date_trunc('field', source)
source 是类型 timestamp 的值表达式(类型 date 和 time 的数值都分别自动转换成timestamp或者interval)。 用 field 选择对该时间戳数值 选用什么样的精度进行截断)。 返回的数值是 timestamp 类型或者interval,所有小于选定的 精度的域都设置为零(或者一,如果是日期和月份域的话)。
field 的有效数值是∶
microseconds |
milliseconds |
second |
minute |
hour |
day |
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
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')。
例子(假设本地时区是 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。
我们可以使用下面的函数获取当前的日期和/或时间∶
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; 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() 是传统的 PostgreSQL 和 CURRENT_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,因此在需要缺省值的时候, 就会使用创建表的时间! 而头两种形式要到实际使用缺省值的时候才计算, 因为它们是函数调用。因此它们可以给出插入时间行的时候 需要的缺省行为。
[1] | 如果操作系统实现了润秒, 那么上限是 60 |