购买
下载掌阅APP,畅读海量书库
立即打开
畅读海量书库
扫码下载掌阅APP

5.7 日期/时间类型

日期/时间类型是最常用的几种数据类型之一,除包括不同日期/时间范围和精度的类型外,还包括了时间间隔类型,本节将详细讲解这些类型的使用方法。

5.7.1 日期/时间类型介绍

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数值的精度也会降低。

5.7.2 日期输入

在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 日期输入的示例

对于中国人来说,使用“/”作为时间和日期分隔符容易产生歧义,最好使用“-”,然后以“年-月-日”的格式输入日期。

5.7.3 时间输入

输入时间时需要注意时区的输入。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)

5.7.4 特殊值

为方便起见,PostgreSQL中用了一些特殊的字符串输入值表示特定的意义,具体见表5-17。

表5-17 日期时间输入的特殊值

5.7.5 函数和操作符列表

日期、时间和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

5.7.6 时间函数

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

5.7.7 extract和date_part函数

extract函数格式如下:


extract (field FROM source)

extract函数从日期/时间数值中抽取子域,比如年、小时等,其返回类型为double precision的数值。source必须是一个timestamp或time或interval类型的值表达式,此外,类型为date的表达式可自动转换为timestamp,因此source也可以用date类型。field是一个标识符或者字符串,它指定从源数据中抽取的域。表5-20中列出了field可以取的各类值及示例。

表5-20 extract函数的示例 2YY3XWuCHgwv1WhuXHVAkXzkVeRBKJEj0NM5Ab7vgPzqRQNkwmB58ynJ4UU0GtM9

点击中间区域
呼出菜单
上一章
目录
下一章
×