postgresql 时间类型和相关函数

今天来好好学习一下postgresql涉及时间的字段类型和一些时间的转换函数

一、时间类型：
名字                                  存储空间    描述                     最低值          最高值            分辨率
timestamp [ (p) ] [without time zone] 8字节      包括日期和时间           4713 BC          5874897 AD        1 毫秒 / 14 位
timestamp [ (p) ] with time zone      8字节      日期和时间，带时区       4713 BC          5874897 AD        1 毫秒 / 14 位
interval [ (p) ]                      12字节      时间间隔                 -178000000 年    178000000 年      1 毫秒 / 14 位
date                                  4字节      只用于日期               4713 BC          32767 AD          1 天
time [ (p) ] [ without time zone ]  8字节      只用于一日内时间         00:00:00        24:00:00          1 毫秒 / 14 位
time [ (p) ] with time zone 12字节      只用于一日内时间，带时区 00:00:00+1359    24:00:00-135     1 毫秒 / 14 位

说明：
1、time ，timestamp 和interval 接受一个可选的精度值 p，这个精度值声明在秒域后面小数点之后保留的位数。 缺省的时候在精度上是没有明确的绑定的， p 有用的范围对 timestamp和 interval 是从 0 到大约 6
2、对于date和timestamp 数值而言， 是自1970-01-01 00:00:00以来的秒数(结果可能是负数）;对于interval数值而言，它是时间间隔的总秒数。

二、当前时间：
test=# select now(),current_timestamp,current_date,current_time;
              now              |              now              |    date    |       timetz      
-------------------------------+-------------------------------+------------+--------------------
2016-01-11 17:22:25.263468+08 | 2016-01-11 17:22:25.263468+08 | 2016-01-11 | 17:22:25.263468+08
(1 row)

说明：
1、current_timestamp和now()查询结果一致
2、.263468+08 前面是精度p(6)，后面是时区+08

补充：
1、去精度
test=# select current_timestamp(0);
      timestamptz      
------------------------
2016-01-11 17:43:19+08
(1 row)

2、改变精度
test=# select current_timestamp(2);
        timestamptz       
---------------------------
2016-01-11 17:45:06.26+08
(1 row)

3、去时区
test=# select current_timestamp(0)::timestamp without time zone;
      timestamp     
---------------------
2016-01-11 17:43:50
(1 row)

4、使用cast函数做类型转换去掉时区
test=# select cast(current_timestamp(0) as timestamp without time zone); 
      timestamp     
---------------------
2016-01-11 17:44:10
(1 row)

三、标准格式
1)日期输入格式
January 8, 1999             在任何datestyle输入模式下都无歧义
1999-01-08             ISO-8601 格式，任何方式下都是1999年1月8号
1/8/1999             歧义，在MDY下是一月八号；在 DMY 模式下读做八月一日
1/18/1999             在MDY模式下读做一月十八日，其它模式下被拒绝
01/02/03             MDY 模式下的2003年一月2日； DMY 模式下的 2003 年 2月 1日； YMD 模式下的2001年二月三日；
1999-Jan-08             任何模式下都是一月8日
Jan-08-1999             任何模式下都是一月8日
08-Jan-1999             任何模式下都是一月8日
99-Jan-08             在 YMD 模式下是一月8日，否则错误
08-Jan-99             一月八日，除了在 YMD 模式下是错误的之外
Jan-08-99             一月八日，除了在 YMD 模式下是错误的之外
19990108             ISO-8601; 任何模式下都是1999年1月8日
990108                     ISO-8601; 任何模式下都是1999年1月8日
1999.008             年和年里的第几天
J2451187             儒略日
January 8, 99 BC     公元前99年

由此可见，date最好是使用1999-01-08、Jan-08-1999、19990108这三类格式，比较通用。

2)时间输入格式
04:05:06.789        ISO 8601
04:05:06        ISO 8601
04:05                ISO 8601
040506                ISO 8601
04:05 AM        与04:05一样；AM不影响数值
04:05 PM        与16:05一样；输入小时数必须 <= 12
04:05:06.789-8        ISO 8601
04:05:06-08:00        ISO 8601
04:05-08:00        ISO 8601
040506-08        ISO 8601

time时间最常用的是04:05:06、04:05:06.789-8（加精度和时区）

3)时区输入格式
PST                 太平洋标准时间(Pacific Standard Time)
America/New_York 完整时区名称
PST8PDT           POSIX风格的时区
-8:00                 ISO-8601与PST的偏移
-800                 ISO-8601与PST的偏移
-8                 ISO-8601与PST的偏移
zulu                 军方对UTC的缩写
z                 zulu的缩写

4)timestamp时间戳输入格式
时间戳类型的有效输入由一个日期和时间的联接组成，后面跟着一个可选的时区(以上三者的结合)因此，1999-01-08 04:05:06和1999-01-08 04:05:06 -8:00都是有效的数值。

5)interval时间间隔输入格式
microseconds   微秒
milliseconds  毫秒
second    秒
minute  分钟
hour    小时
day   天
week   星期
month   月
year   年
decade   十年
century   世纪
millennium  千年
或这些单位的缩写或复数。


interval格式举例：
1-2                        SQL标准格式：一年两个月
3 4:05:06                SQL标准格式：3天4小时5分6秒
1 year 2 months 3 days 4 hours 5 minutes 6 seconds  传统Postgres格式: 1年2个月3天4小时5分钟6秒
P1Y2M3DT4H5M6S                ISO 8601 "带标识符格式": 1年2个月3天4小时5分钟6秒
P0001-02-03T04:05:06        ISO 8601 "缩写格式": 1年2个月3天4小时5分钟6秒


四、各种时间类型举例
test=# create table t1(id serial not null,name varchar(20),rtime date,stime timestamp,utime timestamp with time zone,wtime interval);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
CREATE TABLE

test=# insert into t1(name,rtime,stime,utime,wtime) values('nana','1/6/2015','2015-06-01 00:02:35','2015-06-01 00:02:35+08','1 day');
ERROR:  date/time field value out of range: "1/6/2015"
---报错：date类型不支持1/6/2015,因为会产生歧义
                                                             ^
HINT:  Perhaps you need a different "datestyle" setting.
test=# insert into t1(name,rtime,stime,utime,wtime) values('nana','2015-06-01','2015-06-01 00:02:35','2015-06-01 00:02:35+08','1 day');
INSERT 0 1
test=# insert into t1(name,rtime,stime,utime,wtime) values('tina','2015-11-01','2015-11-01 02:04:00','2015-11-01 02:04:00','6 days 3 hours 35 minutes');
INSERT 0 1
test=# insert into t1(name,rtime,stime,utime,wtime) values('lili','2014-12-24','2014-12-24 15:23:12','2014-12-24 15:23:12+08','1y3m5d');
INSERT 0 1
test=# select * from t1;
id | name |   rtime    |        stime        |         utime          |         wtime         
----+------+------------+---------------------+------------------------+------------------------
  1 | nana | 2015-06-01 | 2015-06-01 00:02:35 | 2015-06-01 00:02:35+08 | 1 day
  2 | tina | 2015-11-01 | 2015-11-01 02:04:00 | 2015-11-01 02:04:00+08 | 6 days 03:35:00         ---utime自动添加了时区
  3 | lili | 2014-12-24 | 2014-12-24 15:23:12 | 2014-12-24 15:23:12+08 | 1 year 5 days 00:03:00   ---3m 被自动判定为分钟minutes，而不是月months，因此还是写具体一点比较好
(3 rows)

类型只写time相当于time with out time zone


五、相关函数
    PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串
以及反过来从格式化的字符串转换成指定的数据类型。

5.1 转换函数                              返回类型    描述                       例子
to_char(timestamp, text)          text        把时间戳转换成字串        to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)            text        把时间间隔转为字串        to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)                text        把整数转换成字串          to_char(125, '999')
to_char(double precision, text)    text        把实数/双精度数转换成字串 to_char(125.8::real, '999D9')
to_char(numeric, text)            text        把numeric转换成字串      to_char(-125.8, '999D99S')
to_date(text, text)                date        把字串转换成日期          to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)          timestamp 把字串转换成时间戳        to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double)              timestamp 把UNIX纪元转换成时间戳    to_timestamp(200120400)
to_number(text, text)              numeric    把字串转换成numeric      to_number('12,454.8-', '99G999D9S')

实例：
postgres=# select current_timestamp,to_char(current_timestamp, 'YYYY-MM-DD HH24:MI');  ---时间戳转换成标准日期格式
              now              |     to_char     
-------------------------------+------------------
2016-01-12 17:18:17.993226+08 | 2016-01-12 17:18

postgres=# select to_char(interval '1 years 2months 5days 7hours 8minutes 9seconds', 'YYYY-MM-DD HH24:MI');  ---时间间隔转换成时间戳
     to_char     
------------------
0001-02-05 07:08
postgres=# select to_char(125,'999');  ---数字125转换成字符串'125'
to_char
---------
125
postgres=# select to_char(125.88,'999');   ---双精度转换成字符串，要注意精度
to_char
---------
  126
postgres=# select to_char(125.88,'999D9');
to_char
---------
  125.9
postgres=# select to_char(125.88,'999D99');
to_char
---------
  125.88
postgres=# select to_char(-125.88,'999D99');
to_char
---------
-125.88
postgres=# select last_record_time from t_sfa_sample_tmp  t limit 1;
last_record_time
------------------
       1442627263
postgres=# select to_timestamp(last_record_time) from t_sfa_sample_tmp  limit 1;
      to_timestamp     
------------------------
2015-09-19 09:47:43+08
postgres=# select to_number('-12,345.67','99G999D99');
to_number
-----------
-12345.67


5.2 用于日期/时间格式化的模式：
模式    描述
HH      一天的小时数(01-12)
HH12    一天的小时数(01-12)
HH24    一天的小时数(00-23)
MI      分钟(00-59)
SS      秒(00-59)
MS      毫秒(000-999)
US      微秒(000000-999999)
AM      正午标识(大写)
Y,YYY    带逗号的年(4和更多位)
YYYY    年(4和更多位)
YYY      年的后三位
YY      年的后两位
Y        年的最后一位
MONTH    全长大写月份名(空白填充为9字符)
Month    全长混合大小写月份名(空白填充为9字符)
month    全长小写月份名(空白填充为9字符)
MON      大写缩写月份名(3字符)
Mon      缩写混合大小写月份名(3字符)
mon      小写缩写月份名(3字符)
MM      月份号(01-12)
DAY      全长大写日期名(空白填充为9字符)
Day      全长混合大小写日期名(空白填充为9字符)
day      全长小写日期名(空白填充为9字符)
DY      缩写大写日期名(3字符)
Dy      缩写混合大小写日期名(3字符)
dy      缩写小写日期名(3字符)
DDD      一年里的日子(001-366)
DD      一个月里的日子(01-31)
D        一周里的日子(1-7；周日是1)
W        一个月里的周数(1-5)(第一周从该月第一天开始)
WW      一年里的周数(1-53)(第一周从该年的第一天开始)

5.3 用于数值格式化的模板模式：
模式     描述
9       带有指定数值位数的值
0       带前导零的值
.(句点) 小数点
,(逗号) 分组(千)分隔符
PR     尖括号内负值
S       带符号的数值
L       货币符号
D       小数点
G       分组分隔符
MI     在指明的位置的负号(如果数字 < 0)
PL     在指明的位置的正号(如果数字 > 0)
SG     在指明的位置的正/负号

5.4 日期/时间函数：
函数                         返回类型                描述                                例子                                   结果
age(timestamp, timestamp)    interval 减去参数，生成一个使用年、月的"符号化"的结果 age('2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days
age(timestamp)               interval 从current_date减去得到的数值                age(timestamp '1957-06-13')               43 years 8 mons 3 days
date_part(text, timestamp)   double   获取子域(等效于extract)                      date_part('hour', timestamp '2001-02-16 20:38:40') 20
date_part(text, interval)     double   获取子域(等效于extract)                      date_part('month', interval '2 years 3 months')    3
date_trunc(text, timestamp)
extract(field from timestamp) double   获取子域 extract(hour from timestamp '2001-02-16 20:38:40') 20
extract(field from interval) double   获取子域 extract(month from interval '2 years 3 months') 3

extract，date_part函数支持的
域          描述                            例子                                                   结果
CENTURY      世纪                         EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');   20
DAY          (月分)里的日期域(1-31)       EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');       16
DECADE      年份域除以10                 EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40');     200
DOW          每周的星期号(0-6;星期天是0)  EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');       5
DOY          一年的第几天(1 -365/366)   EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40');       47
HOUR        小时域(0-23)                 EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40');       20
MICROSECONDS 秒域，包括小数部分，乘以 1,000,000  EXTRACT(MICROSECONDS from TIME '17:12:28.5');     28500000
MILLENNIUM 千年                         EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40'); 3
MILLISECONDS 秒域，包括小数部分，乘以 1000       EXTRACT(MILLISECONDS from TIME '17:12:28.5');     28500
MINUTE      分钟域(0-59)                 EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40');     38
MONTH        对于timestamp数值，它是一年里的月份数(1-12)；
             对于interval数值，它是月的数目，然后对12取模(0-11)   EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40'); 2
QUARTER      该天所在的该年的季度(1-4)   EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');   1  
SECOND      秒域，包括小数部分(0-59[1]) EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40');     40
WEEK        该天在所在的年份里是第几周。 EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40');       7
YEAR        年份域                       EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40');       2001

date_trunc 函数
在概念上和用于数字的trunc 函数类似。
date_trunc('field', source)
source 是类型 timestamp 的值表达式(类型 date 和 time 的数值都分别自动转换成timestamp或者interval)。
用 field 选择对该时间戳数值 选用什么样的精度进行截断)。 返回的数值是 timestamp 类型或者interval，
所有小于选定的 精度的域都设置为零(或者一，如果是日期和月份域的话)。

例子：
postgres=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc     
---------------------
2001-02-16 20:00:00
postgres=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc     
---------------------
2001-01-01 00:00:00


5.5 除了这些函数以外，还支持 SQL 操作符 overlaps
(start1, end1 ) overlaps ( start2, end2)
(start1, length1 ) overlaps ( start2, length2)
这个表达式在两个时间域（用它们的终点定义）重叠的时候生成真值。终点可以以一对日期，时间，或者时间戳来声明;
或者是一个后面跟着一个时间间隔的 日期，时间，时间戳。

postgres=# select (date '2001-02-16', date '2001-12-21') overlaps (date '2001-10-30', date '2002-10-30');  ---两个时间范围重叠
overlaps
----------
t
postgres=# select (date '2001-02-16', interval '100 days') overlaps (date '2001-10-30', date '2002-10-30'); ---两个时间范围未重叠
overlaps
----------
f

六、时间的加减操作
操作                                                         结果
date '2001-09-28' + integer '7'                             date '2001-10-05'
date '2001-09-28' + interval '1 hour'                       timestamp '2001-09-28 01:00'
date '2001-09-28' + time '03:00'                           timestamp '2001-09-28 03:00'
interval '1 day' + interval '1 hour'                       interval '1 day 01:00'
timestamp '2001-09-28 01:00' + interval '23 hours'         timestamp '2001-09-29 00:00'
time '01:00' + interval '3 hours'                           time '04: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 '1 hour'                       timestamp '2001-09-27 23:00'
time '05:00' - time '03:00'                                 interval '02:00'
time '05:00' - interval '2 hours'                           time '03:00'
timestamp '2001-09-28 23:00' - interval '23 hours'         timestamp '2001-09-28 00:00'
interval '1 day' - interval '1 hour'                       interval '23:00'
timestamp '2001-09-29 03:00'-timestamp '2001-09-27 12:00'   interval '1 day 15:00'
interval '1 hour' * double precision '3.5'                 interval '03:30'
interval '1 hour' / double precision '1.5'                 interval '00:40'

七、时区
AT TIME ZONE 构造允许把时间戳转换成不同的时区。

zone 可以声明为文本串（如'PST'）或者一个时间间隔 （比如，INTERVAL '-08:00'）。
postgres=# select current_timestamp ,current_timestamp at time zone 'MST';
              now              |          timezone         
-------------------------------+----------------------------
2016-01-13 10:29:04.426086+08 | 2016-01-12 19:29:04.426086
(1 row)

postgres=# select current_timestamp ,current_timestamp at time zone 'UTC';
              now              |          timezone         
-------------------------------+----------------------------
2016-01-13 10:29:21.187276+08 | 2016-01-13 02:29:21.187276
(1 row)

postgres=# select current_timestamp ,current_timestamp at time zone 'PST';
              now              |          timezone         
-------------------------------+----------------------------
2016-01-13 10:29:39.482202+08 | 2016-01-12 18:29:39.482202
(1 row)
postgres=# select current_timestamp ,current_timestamp at time zone 'CCT';   ----UTC+8
              now              |          timezone         
-------------------------------+----------------------------
2016-01-13 10:34:19.882214+08 | 2016-01-13 10:34:19.882214

MST（UTC-7）
PST（UTC-8）
EST（UTC-5）
CCT 中国时间 (UTC+8)


八、补充：日常中研发很多时候会把时间字段设置成整型，存储时间戳，但是查询时又需要显示成可读的字符串，因此常常需要转换。
1、时间戳转换成字符串：
postgres=# select current_date,cast(extract(epoch FROM current_date) as integer);
    date    | date_part 
------------+------------
2016-01-12 | 1452556800
postgres=# select timestamp without time zone 'epoch' + 1442627263 * interval '1 second';  --不带时区
      ?column?      
---------------------
2015-09-19 01:47:43
postgres=# select timestamp with time zone 'epoch' + 1442627263 * interval '1 second';   --带时区
        ?column?       
------------------------
2015-09-19 09:47:43+08
postgres=# select timestamp with time zone 'epoch' + last_record_time * interval '1 second' from t_sfa_sample_tmp limit 1;  ---结合字段
        ?column?       
------------------------
2015-09-19 09:47:43+08
postgres=# select to_char(to_timestamp(last_record_time), 'YYYY-MM-DD HH24:MI:SS') from t_sfa_sample_tmp  limit 1; ---
       to_char      
---------------------
2015-09-19 09:47:43


2、字符串转换成时间戳：
postgres=# select extract(epoch from timestamp '2015-09-19 09:47:43');    --直接输入timestamp
date_part 
------------
1442656063
postgres=# select extract(epoch from interval '1 years 2 months 5 days 3 hours');   ---直接输入interval
date_part
-----------
  37184400
postgres=# select update_time,extract(epoch from update_time) from off_shell_relation limit 1; ---结合字段
     update_time     | date_part 
---------------------+------------
2015-11-26 19:07:45 | 1448536065