下载掌阅APP，畅读海量书库
立即打开
日期/时间类型是最常用的几种数据类型之一，除包括不同日期/时间范围和精度的类型外，还包括了时间间隔类型，本节将详细讲解这些类型的使用方法。
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函数的示例
