今天来好好学习一下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
一、时间类型：
名字 存储空间 描述 最低值 最高值 分辨率
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
