PostgreSQL 8X时间日期函数.docx
《PostgreSQL 8X时间日期函数.docx》由会员分享,可在线阅读,更多相关《PostgreSQL 8X时间日期函数.docx(10页珍藏版)》请在冰豆网上搜索。
![PostgreSQL 8X时间日期函数.docx](https://file1.bdocx.com/fileroot1/2023-1/8/c30d8c17-5aba-4e79-ac7a-3a7af51bbc71/c30d8c17-5aba-4e79-ac7a-3a7af51bbc711.gif)
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