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

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 世纪。 如果你认为这个不合理,那么请把抱怨发给:梵蒂冈,罗马圣彼得教堂,教皇收。

day

对于timestamp值,(月份)里的日期(1-31);对于interval,天数

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 16
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
Result: 40

decade

年份除以 10

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

dow

每周的星期号,星期天(0)到星期六(6)

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

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

doy

一年的第几天(1-365/366)

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

epoch

对于timestamp with time zone值而言, 是自 1970年01月01日 00: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');
Result: 982384720.12
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 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');
Result: 20

isodow

周中的第几天 [1-7] 星期一:(1)星期天:(7)。

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
Result: 7

除了星期天外,都与dow相同。这与ISO 8601标准周中的第几天编码相匹配。

isoyear

日期中的ISO 8601周编号年(不适用于间隔)。

SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
Result: 2005
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
Result: 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');
Result: 28500000

millennium

千年

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

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

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)

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 西边的时区。 (技术角度讲,PostgreSQL使用UT1,因为不处理闰秒。)

timezone_hour

时区偏移量的小时部分。

timezone_minute

时区偏移量的分钟部分。

week

该天在所在的年份里是第几个ISO 8601周编号的周。 按照定义,ISO周在周一开始并且该年的第一周包含该年的一月四日。换句话说, 一年的第一个星期四在第一周。

在ISO周编号系统里,一月的头几天可能是前一年的第 52 或者第 53 周, 十二月的后几天可能是下一年第一周。比如,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');
Result: 7

year

年份域。要记住这里没有0 AD,所以从AD年里抽取BC年应该小心些。

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

表 9-29. AT TIME ZONE 变体

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

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

注意: 有效的休眠时间间隔精度是平台相关的,通常 0.01 秒是通用的。 休眠的时间将至少等于指定的时间,也有可能由于服务器荷载较重等原因而比指定的时间长。 特别的,pg_sleep_until并不保证能准确的在指定的时间唤醒, 但是它不会提早唤醒。