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

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

表 9.32展示了可用于处理日期/时间值的函数,其细节在随后的小节中描述。表 9.31演示了基本算术操作符 (+*等)的行为。 而与格式化相关的函数,可以参考第 9.8 节。你应该很熟悉第 8.5 节中的日期/时间数据类型的背景知识。

此外, 表 9.1中显示的常用比较操作符也适用于日期/时间类型。 日期和时间戳(带或不带时区)都是可比较的,而时间(带或不带时区)和间隔只能与相同数据类型的其他值进行比较。 将不带时区的时间戳与带时区的时间戳进行比较时,前者的值假定是在TimeZone配置参数指定的时区中给出的,并被转换到UTC,以便与后者的值进行比较(其已经在内部用UTC)。 类似地,日期值会被假定表示TimeZone区域中的午夜,当它与时间戳进行比较时。

所有下文描述的接受timetimestamp输入的函数和操作符实际上都有两种变体: 一种接收time with time zonetimestamp with time zone, 另外一种接受time without time zone或者 timestamp without time zone。 为了简化,这些变种没有被独立地展示。 此外,+*操作符都是可交换的操作符对(例如,date + integerinteger + date);我们只显示每一对中的一个。

表 9.31. 日期/时间操作符

操作符

描述

例子

date + integerdate

给日期加上天数

date '2001-09-28' + 72001-10-05

date + intervaltimestamp

为日期添加时间间隔

date '2001-09-28' + interval '1 hour'2001-09-28 01:00:00

date + timetimestamp

在日期中添加一天中的时间

date '2001-09-28' + time '03:00'2001-09-28 03:00:00

interval + intervalinterval

添加时间间隔

interval '1 day' + interval '1 hour'1 day 01:00:00

timestamp + intervaltimestamp

在时间戳中添加一个时间间隔

timestamp '2001-09-28 01:00' + interval '23 hours'2001-09-29 00:00:00

time + intervaltime

为时间添加时间间隔

time '01:00' + interval '3 hours'04:00:00

- intervalinterval

取否一个时间间隔

- interval '23 hours'-23:00:00

date - dateinteger

减去日期,生成经过的天数

date '2001-10-01' - date '2001-09-28'3

date - integerdate

从日期中减去天数

date '2001-10-01' - 72001-09-24

date - intervaltimestamp

从日期中减去时间间隔

date '2001-09-28' - interval '1 hour'2001-09-27 23:00:00

time - timeinterval

减去时间

time '05:00' - time '03:00'02:00:00

time - intervaltime

从时间中减去时间间隔

time '05:00' - interval '2 hours'03:00:00

timestamp - intervaltimestamp

从时间戳中减去时间间隔

timestamp '2001-09-28 23:00' - interval '23 hours'2001-09-28 00:00:00

interval - intervalinterval

减去时间间隔

interval '1 day' - interval '1 hour'1 day -01:00:00

timestamp - timestampinterval

减去时间戳(将24小时间隔转换为天,类似于justify_hours()

timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'63 days 15:00:00

interval * double precisioninterval

将时间间隔乘以数量

interval '1 second' * 90000:15:00

interval '1 day' * 2121 days

interval '1 hour' * 3.503:30:00

interval / double precisioninterval

用时间间隔除以数量

interval '1 hour' / 1.500:40:00


表 9.32. 日期/时间函数

函数

描述

例子

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')62 years 6 mons 10 days

clock_timestamp ( ) → timestamp with time zone

当前日期和时间(在语句执行期间变化);参见第 9.9.5 节

clock_timestamp()2019-12-23 14:39:53.662522-05

current_datedate

当前日期;参见 第 9.9.5 节

current_date2019-12-23

current_timetime with time zone

一天中的当前时间;参见 第 9.9.5 节

current_time14:39:53.662522-05

current_time ( integer ) → time with time zone

一天中的当前时间;有限精度;参见 第 9.9.5 节

current_time(2)14:39:53.66-05

current_timestamptimestamp with time zone

当前日期和时间 (当前事务的开始);参见 第 9.9.5 节

current_timestamp2019-12-23 14:39:53.662522-05

current_timestamp ( integer ) → timestamp with time zone

当前日期和时间 (当前事务的开始);有限精度;参见 第 9.9.5 节

current_timestamp(0)2019-12-23 14:39:53-05

date_bin ( interval, timestamp, timestamp ) → timestamp

Bin输入到指定的间隔与指定的原点对齐;参见第 9.9.3 节

date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')2001-02-16 20:35:00

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

date_trunc ( text, timestamp with time zone, text ) → timestamp with time zone

在规定的时区中截断到指定的精度;参见 第 9.9.2 节

date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')2001-02-16 13:00:00+00

date_trunc ( text, interval ) → interval

截断到指定的精度;参见 第 9.9.2 节

date_trunc('hour', interval '2 days 3 hours 40 minutes')2 days 03:00:00

extract ( field from timestamp ) → numeric

获取时间戳子字段;参见 第 9.9.1 节

extract(hour from timestamp '2001-02-16 20:38:40')20

extract ( field from interval ) → numeric

获取时间间隔子字段;参见 第 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 'infinity')false

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_daysjustify_hours调整时间间隔; 通过额外的符号调整

justify_interval(interval '1 mon -1 hour')29 days 23:00:00

localtimetime

一天中当前时间;参见 第 9.9.5 节

localtime14:39:53.662522

localtime ( integer ) → time

一天中的当前时间,有限精度;参见 第 9.9.5 节

localtime(0)14:39:53

localtimestamptimestamp

当前日期和时间(当前事务的开始);参见 第 9.9.5 节

localtimestamp2019-12-23 14:39:53.662522

localtimestamp ( integer ) → timestamp

当前日期和时间(当前事务的开始);有限精度;参见 第 9.9.5 节

localtimestamp(2)2019-12-23 14:39:53.66

make_date ( year int, month int, day int ) → date

从年、月和日字段创建日期(负数年份表示BC)

make_date(2013, 7, 15)2013-07-15

make_interval ( [ years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]] ) → interval

从年、月、周、日、小时、分钟和秒字段创建时间间隔,每个字段默认为0

make_interval(days => 10)10 days

make_time ( hour int, min int, sec double precision ) → time

从小时、分钟和秒字段创建时间

make_time(8, 15, 23.5)08:15:23.5

make_timestamp ( year int, month int, day int, hour int, min int, sec double precision ) → timestamp

从年、月、日、小时、分钟和秒字段创建时间戳(负数年份表示BC)

make_timestamp(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5

make_timestamptz ( year int, month int, day int, hour int, min int, sec double precision [, timezone text ] ) → timestamp with time zone

从年,月,日,小时,分钟和秒字段结合时区创建时间戳(负数年份表示BC)。 如果没有指定timezone,则使用当前时区;示例假设会话时区为Europe/London

make_timestamptz(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5+01

make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York')2013-07-15 13:15:23.5+01

now ( ) → timestamp with time zone

当前日期和时间(当前事务的开始);参见 第 9.9.5 节

now()2019-12-23 14:39:53.662522-05

statement_timestamp ( ) → timestamp with time zone

当前日期和时间(当前语句的开始);参见 第 9.9.5 节

statement_timestamp()2019-12-23 14:39:53.662522-05

timeofday ( ) → text

当前的日期和时间 (类似 clock_timestamp, 但是采用 text 字符串);参见 第 9.9.5 节

timeofday()Mon Dec 23 14:39:53.662522 2019 EST

transaction_timestamp ( ) → timestamp with time zone

当前日期和时间(当前事务的开始);参见 第 9.9.5 节

transaction_timestamp()2019-12-23 14:39:53.662522-05

to_timestamp ( double precision ) → timestamp with time zone

将Unix纪元转换为带时区的时间戳(从1970-01-01 00:00:00+00开的的秒)

to_timestamp(1284352323)2010-09-13 04:32:03+00


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

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

这个表达式在两个时间域(用它们的端点定义)重叠的时候得到真,当它们不重叠时得到假。端点可以用一对日期、时间或者时间戳来指定;或者是用一个后面跟着一个间隔的日期、时间或时间戳来指定。当一对值被提供时,起点或终点都可以被写在前面,OVERLAPS会自动地把较早的值作为起点。每一个时间段被认为是表示半开的间隔start <= time < end,除非startend相等,这种情况下它表示单个时间实例。例如这表示两个只有一个共同端点的时间段不重叠。

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必须是一个类型 timestamptimeinterval的值表达式(类型为date的表达式将被造型为 timestamp,并且因此也可以被同样使用)。 field是一个标识符或者字符串,它指定从源值中抽取的域。 extract函数返回类型为numeric的值。 下列值是有效的域名字∶

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 AD 开始, 尽管那时候人们还不知道这是第一个世纪。这个定义适用于所有使用格里高利历法的国家。其中没有 0 世纪,我们直接从公元前 1 世纪到公元 1 世纪。 如果你认为这个不合理,那么请把抱怨发给:罗马圣彼得教堂,梵蒂冈,教皇收。

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 and timestamp值,是自 1970-01-01 00:00:00 以来的最小秒数,不考虑时区或夏令时规则; 对于interval值,它是时间间隔的总秒数。

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

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
Result: 982355920.12

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

不能用to_timestamp把一个 epoch 值转换回成timestamp with time zone

SELECT to_timestamp(982384720.12);
Result: 2001-02-17 04:38:40.12+00

当心,应用to_timestamp到一个从datetimestamp值提取的纪元,可能会产生误导的结果: 结果将实际上假设原始值是以UTC给出的,而实际情况可能并非如此。

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之前的版本。

julian

与日期或时间戳相对应的Julian Date(不适用于间隔)。 不是本地午夜的时间戳产生一个小数值。 参阅第 B.7 节以了解更多信息。

SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
Result: 2453737
SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
Result: 2453737.50000000000000000000
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

19xx的年份在第二个千年里。第三个千年从 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');
结果: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

秒字段,包括任何小数秒。

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不使用 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对于单调增的域(epochjulianyearisoyeardecadecentury以及millennium)返回 +/-Infinity。对于其他域返回 NULL。PostgreSQL 9.6 之前的版本对所有输入无穷的情况都返回零。

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

在传统的Ingres上建模的date_part函数等价于SQL标准函数extract

date_part('field', source)

请注意这里的field参数必须是一个串值,而不是一个名字。 有效的date_part域名 和extract相同。 由于历史原因,date_part函数返回double precision类型的值。 这可能导致在某些使用中损失精度。 建议使用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是类型timestampinterval的值表达式(类型datetime的值都分别被自动转换成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 zoneinterval输入时,不能指定时区。 这些总是按表面值来处理。

示例 (假设当地时区为 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. date_bin

函数date_bin bins输入时间戳到指定的间隔(stride)与指定的原点对齐。

date_bin(stride, source, origin)

sourcetimestamptimestamp with time zone类型的值表达式。 (类型date的值会自动转换为timestamp。) strideinterval类型的值表达式。 返回值同样是timestamptimestamp with time zone类型,并且它标记着放置source的bin的开始。

示例:

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30

在完整单位(1分钟,1小时,等等)的情况下,它给出与类似 date_trunc调用相同的结果,但不同的地方是date_bin可以截断到任意间隔。

stride 时间间隔必须大于零,并且不能包含月或更大的单位。

9.9.4. AT TIME ZONE

AT TIME ZONE把时间戳without时区转换成时间戳with时区或者反过来,并且把time with time zone值转换成不同的时区。表 9.33展示了它的变体。

表 9.33. AT TIME ZONE变体

操作符

描述

例子

timestamp without time zone AT TIME ZONE zonetimestamp with time zone

将给定的时间戳without时区转换为时间戳with时区,假设给定的值在指定的时区内。

timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'2001-02-17 03:38:40+00

timestamp with time zone AT TIME ZONE zonetimestamp without time zone

将给定的时间戳with时区转换为时间戳without时区,因为时间将出现在该时区中

timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver'2001-02-16 18:38:40

time with time zone AT TIME ZONE zonetime with time zone

将给定的时间with时区转换为新的时区。由于没有提供日期,这将使用指定目的区域的当前活动UTC偏移量。

time with time zone '05:34:17-05' at time zone 'UTC'10:34:17+00


在这些表达式里,我们需要的时区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)等效于 SQL 兼容的结构timestamp AT TIME ZONE zone

9.9.5. 当前日期/时间

PostgreSQL提供了许多返回当前日期和时间的函数。这些 SQL 标准的函数全部都按照当前事务的开始时刻返回值:

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可以有选择地接受一个精度参数, 该精度导致结果的秒域被园整为指定小数位。如果没有精度参数,结果将被给予所能得到的全部精度。

一些例子:

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.6. 延时执行

下面的这些函数可以用于让服务器进程延时执行:

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或者其变体时,你的会话没有持有不必要 的锁。否则其它会话可能必须等待你的休眠会话,因而减慢整个系统速度。