日期/时间类型是最常用的几种数据类型之一,除包括不同日期/时间范围和精度的类型外,还包括了时间间隔类型,本节将详细讲解这些类型的使用方法。
PostgreSQL中有关日期和时间的数据类型见表5-14。
表5-14 日期/时间类型列表
需要注意的是,PostgreSQL中的时间类型可以精确到秒以下,而MySQL中的时间类型只能精确到秒。time、timestamp、interval接受一个可选的精度值p以指明秒域中小数部分的位数。如果没有明确的默认精度,对于timestamp和interval类型,p的取值范围是0~6。
timestamp数值是以双精度浮点数的方式存储的,它以2000-01-01午夜之前或之后的秒数存储。可以想象,在2000-01-01前后几年的日期中精度是可以达到微秒的,而在更远一些的日子,精度可能达不到微秒,但达到毫秒是没有问题的。
也可以改变编译选项使timestamp以八字节整数的方式存储,那么微秒的精度就可以在数值的全部范围内得到保证,不过这样一来八位整数的时间戳范围就缩小到了4713 BC到294276 AD之间。此外,这个编译选项也决定了time和interval数值是保存成浮点数还是八字节整数。同样,在以浮点数存储的时候,随着时间间隔的增加,interval数值的精度也会降低。
在SQL中,任何日期或者时间的文本输入都需要由“日期/时间”类型加单引号括起来的字符串组成,语法如下:
type [ (p) ] 'value'
日期和时间的输入几乎可以是任何合理的格式,包括ISO-8601格式、SQL-兼容格式、传统的Postgres格式及其他形式。对于一些格式,日期输入中的月和日可能会使人产生疑惑,因此系统支持自定义这些字段的顺序。如果DateStyle参数默认为“MDY”,则表示按“月-日-年”的格式进行解析,如果参数设置为“DMY”,则按照“日-月-年”的格式进行解析,设置为,“YMD”表示按照“年-月-日”的格式进行解析。示例如下:
osdba=# create table t(col1 date); CREATE TABLE osdba=# insert into t values(date '12-10-2010'); INSERT 0 1 osdba=# select * from t; col1 ------------ 2010-12-10 (1 row) osdba=# show datestyle; DateStyle ----------- ISO, MDY (1 row) osdba=# set datestyle='YMD'; SET osdba=# insert into t values(date '2010-12-11'); INSERT 0 1 osdba=# select * from t; col1 ------------ 2010-12-10 2010-12-11 (2 rows)
更多的日期输入示例如表5-15所示。
表5-15 日期输入的示例
对于中国人来说,使用“/”作为时间和日期分隔符容易产生歧义,最好使用“-”,然后以“年-月-日”的格式输入日期。
输入时间时需要注意时区的输入。time被认为是time without time zone的类型,这样即使字符串中有时区也会被怱略,示例如下:
osdba=# select time '04:05:06'; time ---------- 04:05:06 (1 row) osdba=# select time '04:05:06 PST'; time ---------- 04:05:06 (1 row) osdba=# select time with time zone'04:05:06 PST'; timetz ------------- 04:05:06-08 (1 row)
时间字符串可以使用冒号作分隔符,即输入格式为“hh:mm:ss”,如“10:23:45”,也可以不用分隔符,如“102345”表示10点23分45秒。
更多的时间类型的输入示例见表5-16。
表5-16 时间输入的示例
最好不要用时区缩写来表示时区,因为这样有可能给阅读者带来困扰,如CST时间有可能有以下几种含义:
·Central Standard Time (USA) UT-6:00,即美国标准时间。
·Central Standard Time (Australia) UT+9:30,即澳大利亚标准时间。
·China Standard Time UT+8:00,即中国标准时间。
·Cuba Standard Time UT-4:00,即古巴标准时间。
这么多的时区都叫CST,是不是让人困惑?CST在PostgreSQL中代表Central Standard Time (USA) UT-6:00,缩写可以查询视图“pg_timezone_abbrevs”:
osdba=# select * from pg_timezone_abbrevs where abbrev='CST'; abbrev | utc_offset | is_dst --------+------------+-------- CST | -06:00:00 | f (1 row)
在输入的时间后加“AT TIME ZONE”可以转换或指定时区:
osdba=# SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '+08:00'; timezone --------------------- 2001-02-16 17:38:40 (1 row)
为方便起见,PostgreSQL中用了一些特殊的字符串输入值表示特定的意义,具体见表5-17。
表5-17 日期时间输入的特殊值
日期、时间和inteval类型数值之间可以进行加减乘除运算,具体见表5-18。
表5-18 日期和时间操作符
日期、时间和inteval类型的函数见表5-19。
除了以上函数以外,PostgreSQL还支持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
PostgreSQL提供了许多用于返回当前日期和时间的函数。下面的函数都是按照当前事务开始的时间返回结果的:
·CURRENT_DATE。
·CURRENT_TIME。
·CURRENT_TIMESTAMP。
·CURRENT_TIME(precision)。
·CURRENT_TIMESTAMP(precision)。
表5-19 日期/时间函数
·LOCALTIME。
·LOCALTIMESTAMP。
·LOCALTIME(precision)。
·LOCALTIMESTAMP(precision)。
·now()。
·transaction_timestamp()。
其中,CURRENT_TIME和CURRENT_TIMESTAMP返回带时区的值;LOCALTIME和LOCALTIMESTAMP返回不带时区的值。
CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP可以选择性地给予一个精度参数,该精度会导致结果的秒数域被四舍五入到指定的小数位。如果没有精度参数,将给予所能得到的全部精度。示例如下:
osdba=# begin; BEGIN osdba=# SELECT CURRENT_TIME; timetz -------------------- 11:59:58.280049+08 (1 row) osdba=# SELECT CURRENT_DATE; date ------------ 2011-05-02 (1 row) osdba=# SELECT CURRENT_TIMESTAMP; now ------------------------------- 2011-05-02 11:59:58.280049+08 (1 row) osdba=# SELECT CURRENT_TIMESTAMP(2); timestamptz --------------------------- 2011-05-02 11:59:58.28+08 (1 row) osdba=# SELECT LOCALTIMESTAMP; timestamp ---------------------------- 2011-05-02 11:59:58.280049 (1 row) osdba=# end; COMMIT
因为这些函数全部是按照当前事务开始的时间返回结果的,所以它们的值在整个事务运行期间都不会改变。PostgreSQL这样做是为了允许一个事务在“当前时间”上有连贯的概念,这样同一个事务里的多个修改就可以保持同样的时间戳了。
PostgreSQL同样也提供了返回实时时间值的函数,它们的返回值会在事务中随时间的推移而不断变化。这些函数列表如下:
·statement_timestamp()。
·clock_timestamp()。
·timeofday()。
now()函数、CURRENT_TIMESTAMP函数和transaction_timestamp()函数是等效的。不过,transaction_timestamp()的命名更准确地表明了其含义。statement_timestamp()返回当前语句开始时刻的时间戳。statement_timestamp()和transaction_timestamp()在一个事务的第一条命令里的返回值相同,但是在随后的命令中返回结果却不一定相同。clock_timestamp()返回实时时钟的当前时间戳,因此它的值甚至在同一条SQL命令中都会变化。timeofday()相当于clock_timestamp(),也返回实时时钟的当前时间戳,但它返回的是一个text字符串,而不是timestamp with time zone值。
所有日期/时间类型还接受特殊的文本值“now”,用于声明当前的日期和时间(重申:乃当前事务开始的时间)。因此,下面3个语句都返回相同的结果:
·SELECT CURRENT_TIMESTAMP。
·SELECT now()。
·SELECT TIMESTAMP with time zone 'now'。
示例如下:
osdba=# begin; BEGIN osdba=# SELECT CURRENT_TIMESTAMP; now ------------------------------- 2011-05-02 12:26:13.679925+08 (1 row) osdba=# SELECT now(); now ------------------------------- 2011-05-02 12:26:13.679925+08 (1 row) osdba=# SELECT TIMESTAMP with time zone 'now'; timestamptz ------------------------------- 2011-05-02 12:26:13.679925+08 (1 row) osdba=# end; COMMIT
extract函数格式如下:
extract (field FROM source)
extract函数从日期/时间数值中抽取子域,比如年、小时等,其返回类型为double precision的数值。source必须是一个timestamp或time或interval类型的值表达式,此外,类型为date的表达式可自动转换为timestamp,因此source也可以用date类型。field是一个标识符或者字符串,它指定从源数据中抽取的域。表5-20中列出了field可以取的各类值及示例。
表5-20 extract函数的示例