PostgreSQL 8X时间日期函数.docx
《PostgreSQL 8X时间日期函数.docx》由会员分享，可在线阅读，更多相关《PostgreSQL 8X时间日期函数.docx（10页珍藏版）》请在冰豆网上搜索。
PostgreSQL8X时间日期函数
PostgreSQL8.X时间日期函数
2011-03-1610:
16:
11|分类：
postgreSQL|字号订阅
9.9.时间/日期函数和操作符
Table9-26显示了PostgreSQL里可以用于处理日期/时间数值的函数，随后一节里描述了细节。
Table9-25演示了基本算术操作符（+，*等等）的行为。
而与格式化相关的函数，可以参考Section9.8。
你应该很熟悉来自Section8.5的日期/时间数据类型的背景知识。
所有下面描述的函数和操作符接收的time或者timestamp输入实际上都来自两种可能：
一个是接收timewithtimezone或timestampwithtimezone，另外一种是timewithouttimezone或者timestampwithouttimezone。
出于简化考虑，这些变种没有独立显示出来。
还有，+和*操作符都是以可交换的操作符对方式存在的（比如，date+integer和integer+date）；我们只显示了这样的交换操作符对中的一个。
Table9-25.日期/时间操做符
操作符
例子
结果
+
date'2001-09-28'+integer'7'
date'2001-10-05'
+
date'2001-09-28'+interval'1hour'
timestamp'2001-09-2801:
00'
+
date'2001-09-28'+time'03:
00'
timestamp'2001-09-2803:
00'
+
interval'1day'+interval'1hour'
interval'1day01:
00'
+
timestamp'2001-09-2801:
00'+interval'23hours'
timestamp'2001-09-2900:
00'
+
time'01:
00'+interval'3hours'
time'04:
00'
-
-interval'23hours'
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'1hour'
timestamp'2001-09-2723:
00'
-
time'05:
00'-time'03:
00'
interval'02:
00'
-
time'05:
00'-interval'2hours'
time'03:
00'
-
timestamp'2001-09-2823:
00'-interval'23hours'
timestamp'2001-09-2800:
00'
-
interval'1day'-interval'1hour'
interval'23:
00'
-
timestamp'2001-09-2903:
00'-timestamp'2001-09-2712:
00'
interval'1day15:
00'
*
interval'1hour'*doubleprecision'3.5'
interval'03:
30'
/
interval'1hour'/doubleprecision'1.5'
interval'00:
40'
Table9-26.日期/时间函数
函数
返回类型
描述
例子
结果
age（timestamp,timestamp）
interval
减去参数，生成一个使用年、月的"符号化"的结果
age（'2001-04-10',timestamp'1957-06-13'）
43years9mons27days
age（timestamp）
interval
从current_date减去得到的数值
age（timestamp'1957-06-13'）
43years8mons3days
current_date
date
今天的日期；见Section9.9.4
current_time
timewithtimezone
现在的时间；见Section9.9.4
current_timestamp
timestampwithtimezone
日期和时间；见Section9.9.4
date_part（text,timestamp）
doubleprecision
获取子域（等效于extract）；又见Section9.9.1
date_part（'hour',timestamp'2001-02-1620:
38:
40'）
20
date_part（text,interval）
doubleprecision
获取子域（等效于extract）；又见Section9.9.1
date_part（'month',interval'2years3months'）
3
date_trunc（text,timestamp）
timestamp
截断成指定的精度；又见Section9.9.2
date_trunc（'hour',timestamp'2001-02-1620:
38:
40'）
2001-02-1620:
00:
00+00
extract（fieldfromtimestamp）
doubleprecision
获取子域；又见Section9.9.1
extract（hourfromtimestamp'2001-02-1620:
38:
40'）
20
extract（fieldfrominterval）
doubleprecision
获取子域；又见Section9.9.1
extract（monthfrominterval'2years3months'）
3
isfinite（timestamp）
boolean
测试有穷时间戳（非无穷）
isfinite（timestamp'2001-02-1621:
28:
30'）
true
isfinite（interval）
boolean
测试有穷时间间隔
isfinite（interval'4hours'）
true
localtime
time
今日的时间；见Section9.9.4
localtimestamp
timestamp
日期和时间；见Section9.9.4
now（）
timestampwithtimezone
当前的日期和时间（等效于current_timestamp）；见Section9.9.4
timeofday（）
text
当前日期和时间；见Section9.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'100days'）OVERLAPS（DATE'2001-10-30',DATE'2002-10-30'）;
Result:
false
9.9.1.EXTRACT，date_part
EXTRACT（fieldfromsource）
extract函数从日期/时间数值里抽取子域，比如年或者小时等。
source必须是一个类型timestamp，time，或者interval的值表达式。
（类型为date的表达式将转换为timestamp，因此也可以用。
）field是一个标识符或者字串，它指定从源数据中抽取的数域。
extract函数返回类型为doubleprecision的数值。
下列数值是有效数据域的名字∶
century
世纪。
SELECTEXTRACT（CENTURYFROMTIMESTAMP'2000-12-1612:
21:
13'）;Result:
20
SELECTEXTRACT（CENTURYFROMTIMESTAMP'2001-02-1620:
38:
40'）;Result:
21
第一个世纪从0001-01-0100:
00:
00AD开始，尽管那时候人们还不知道这是第一个世纪。
这个定义适用于所有使用格里高利历法的国家。
没有0世纪，我们直接从公元前1世纪到公元1世纪。
如果你认为这个不合理，那么请把抱怨发给：
罗马圣彼得教堂，梵蒂冈，教皇收。
PostgreSQL8.0以前版本里并不遵循世纪的习惯编号，只是把年份除以100。
day
（月分）里的日期域（1-31）
SELECTEXTRACT（DAYfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
16
decade
年份域除以10
SELECTEXTRACT（DECADEfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
200
dow
每周的星期号（0-6；星期天是0）（仅用于timestamp）
SELECTEXTRACT（DOWFROMTIMESTAMP'2001-02-1620:
38:
40'）;Result:
5
请注意extract的星期几编号和to_char函数的不同。
doy
一年的第几天（1-365/366）（仅用于timestamp）
SELECTEXTRACT（DOYfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
47
epoch
对于date和timestamp数值而言，是自1970-01-0100:
00:
00以来的秒数（结果可能是负数。
）；对于interval数值而言，它是时间间隔的总秒数。
SELECTEXTRACT（EPOCHfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
982352320
SELECTEXTRACT（EPOCHfromINTERVAL'5days3hours'）;Result:
442800
下面是把epoch值转换回时间戳的方法：
SELECTTIMESTAMPWITHTIMEZONE'epoch'+982384720*INTERVAL'1second';
hour
小时域（0-23）
SELECTEXTRACT（HOURfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
20
microseconds
秒域，包括小数部分，乘以1,000,000。
请注意它包括全部的秒。
SELECTEXTRACT（MICROSECONDSfromTIME'17:
12:
28.5'）;Result:
28500000
millennium
千年。
SELECTEXTRACT（MILLENNIUMfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
3
20世纪（19xx年）里面的年份在第二个千年里。
第三个千年从2001年一月一日开始。
PostgreSQL8.0之前的版本并不遵循前年编号的习惯，只是返回年份除以1000。
milliseconds
秒域，包括小数部分，乘以1000。
请注意它包括完整的秒。
SELECTEXTRACT（MILLISECONDSfromTIME'17:
12:
28.5'）;Result:
28500
minute
分钟域（0-59）
SELECTEXTRACT（MINUTEfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
38
month
对于timestamp数值，它是一年里的月份数（1-12）；对于interval数值，它是月的数目，然后对12取模（0-11）
SELECTEXTRACT（MONTHfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
2
SELECTEXTRACT（MONTHfromINTERVAL'2years3months'）;
Result:
3
SELECTEXTRACT（MONTHfromINTERVAL'2years13months'）;
Result:
1
quarter
该天所在的该年的季度（1-4）（仅用于timestamp）
SELECTEXTRACT（QUARTERfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
1
second
秒域，包括小数部分（0-59[1]）
SELECTEXTRACT（SECONDfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
40
SELECTEXTRACT（SECONDfromTIME'17:
12:
28.5'）;
Result:
28.5
timezone
与UTC的时区偏移，以秒记。
正数对应UTC东边的时区，负数对应UTC西边的时区。
timezone_hour
时区偏移的小时部分。
timezone_minute
时区偏移的分钟部分。
week
该天在所在的年份里是第几周。
根据定义（ISO8601），一年的第一周包含该年的一月四日。
（ISO-8601的周从星期一开始。
）换句话说，一年的第一个星期四在第一周。
（只用于timestamp数值）。
SELECTEXTRACT（WEEKfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
7
year
年份域。
要记住这里没有0AD，所以从AD年里抽取BC年应该小心些。
SELECTEXTRACT（YEARfromTIMESTAMP'2001-02-1620:
38:
40'）;Result:
2001
extract函数主要的用途是做运算用。
对于用于显示的日期/时间数值格式化，参阅Section9.8。
date_part函数是在传统的Ingres函数的基础上制作的（该函数等效于SQL标准函数extract）∶
date_part（'field',source）
请注意这里的field参数必须是一个字串值，而不是一个名字。
有效的date_part数域名和用于extract的是一样的。
SELECTdate_part（'day',TIMESTAMP'2001-02-1620:
38:
40'）;
Result:
16
SELECTdate_part（'hour',INTERVAL'4hours3minutes'）;
Result:
4
9.9.2.date_trunc
date_trunc函数在概念上和用于数字的trunc函数类似。
date_trunc（'field',source）
source是类型timestamp的值表达式（类型date和time的数值都分别自动转换成timestamp或者interval）。
用field选择对该时间戳数值选用什么样的精度进行截断）。
返回的数值是timestamp类型或者interval，所有小于选定的精度的域都设置为零（或者一，如果是日期和月份域的话）。
field的有效数值是∶
microseconds
milliseconds
second
minute
hour
day
week
month
year
decade
century
millennium
例子：
SELECTdate_trunc（'hour',TIMESTAMP'2001-02-1620:
38:
40'）;Result:
2001-02-1620:
00:
00+00
SELECTdate_trunc（'year',TIMESTAMP'2001-02-1620:
38:
40'）;Result:
2001-01-0100:
00:
00+00
9.9.3.ATTIMEZONE
ATTIMEZONE构造允许把时间戳转换成不同的时区。
Table9-27显示了其变体。
Table9-27.ATTIMEZONE变体
表达式
返回类型
描述
timestampwithouttimezoneATTIMEZONEzone
timestampwithtimezone
把给定时区的当地时间转换成UTC
timestampwithtimezoneATTIMEZONEzone
timestampwithouttimezone
把UTC转换成给定时区的当地时间
timewithtimezoneATTIMEZONEzone
timewithtimezone
在时区之间转换当地时间
在这些表达式里，我们需要的zone可以声明为文本串（比如，'PST'）或者一个时间间隔（比如，INTERVAL'-08:
00'）。
在文本的情况下，可用的时区名字在TableB-4里显示。
（可能支持更通用的TableB-6会更好些，不过目前这些还没有实现。
）
例子（假设本地时区是PST8PDT）：
SELECTTIMESTAMP'2001-02-1620:
38:
40'ATTIMEZONE'MST';Result:
2001-02-1619:
38:
40-08
SELECTTIMESTAMPWITHTIMEZONE'2001-02-1620:
38:
40-05'ATTIMEZONE'MST';
Result:
2001-02-1618:
38:
40
第一个例子接受一个无时区的时间戳然后把她解释成MST时间（UTC-7）生成UTC时间戳，然后这个时间转换为PST（UTC-8）来显示。
第二个例子接受一个声明为EST（UTC-5）的时间戳，然后把它转换成MST（UTC-7）的当地时间。
函数timezone（zone,timestamp）等效于SQL兼容的构造timestampATTIMEZONEzone。
9.9.4.当前日期/时间
我们可以使用下面的函数获取当前的日期和/或时间∶
CURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPCURRENT_TIME（precision）CURRENT_TIMESTAMP（precision）LOCALTIMELOCALTIMESTAMPLOCALTIME（precision）LOCALTIMESTAMP（precision）
CURRENT_TIME和CURRENT_TIMESTAMP带有时区值；LOCALTIME和LOCALTIMESTAMP的数值没有时区值。
CURRENT_TIME，CURRENT_TIMESTAMP，LOCALTIME和LOCALTIMESTAMP可以有选择地给予一个精度参数，该精度导致结果的秒数域园整为指定小数位。
如果没有精度参数，将给予所能得到的全部精度。
注意:
在PostgreSQL7.2之前没有实现精度参数，结果总是给出整数的秒。
一些例子：
SELECTCURRENT_TIME;
Result:
14:
39:
53.662522-05
SELECTCURRENT_DATE;
Result:
2001-12-23
SELECTCURRENT_TIMESTAMP;
Result:
2001-12-2314:
39:
53.662522-05
SELECTCURRENT_TIMESTAMP
（2）;
Result:
2001-12-2314:
39:
53.66-05
SELECTLOCALTIMESTAMP;
Result:
2001-12-2314:
39:
53.662522
函数now（）是传统的PostgreSQL和CURRENT_TIMESTAMP的等效物。
还有一个timeofday（）函数，由于历史原因，它返回一个字串，而不是timestamp值∶
SELECTtimeofday（）;Result:
SatFeb1719:
07:
32.0001262001EST
还有一件事提醒大家，那就是CURRENT_TIMESTAMP和相关的函数把时间当做当前事务的开始返回；在事务运行的时候，它们的数值并不改变。
我们认为这是一个特性：
目的是为了允许一个事务在"当前"时间上有连贯的概念，这样在同一个事务离得多个修改可以有同样的时间戳。
但timeofday（）返回当前的实际时间，并且随着事务的处理会前进。
注意:
许多其它数据库系统更频繁地更新这些数值。
所有日期/时间类型还接受特殊的文本值now，用于声明当前的日期和时间。
因此，下面三个都返回相同的结果∶
SELECTCURRENT_TIMESTAMP;
SELECTnow（）;SELECTTIMESTAMP'now';
提示:
在创建表声明一个DEFAULT值的时候你是不会想用第三种形式的。
因为系统将在分析这个常量的时候把now转换成一个timestamp，因此在需要缺省值的时候，就会使用创建表的时间!
而头两种形式要到实际使用缺省值的时候才计算，因为它们是函数调用。
因此它们可以给出插入时间行的时候需要的缺省行为。
如果操作系统实现了润秒，那么上限是60
升级会员 