本文共 13524 字，大约阅读时间需要 45 分钟。
今天来好好学习一下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 转载地址：http://igeno.baihongyu.com/