数据库中日期、时间、时间戳的问题（未完待续）

最近在Postgresql中导入数据，遇到时间问题，将数据库中的时间进行梳理下：

1.基本概念

1.1 日期(date)：也就是常说的年、月、日，以下为合法的日期格式列表：

例子	描述
January 8, 1999	在任何datestyle输入模式下都无歧义
1999-01-08	ISO-8601格式，任何方式下都是1999年1月8号，(建议格式)
1/8/1999	歧义，在MDY下是1月8号；在 DMY模式下读做8月1日
1/18/1999	在MDY模式下读做1月18日，其它模式下被拒绝
01/02/03	MDY模式下的2003年1月2日；DMY模式下的2003年2月1日；YMD 模式下的2001年2月3日
1999-Jan-08	任何模式下都是1月8日
Jan-08-1999	任何模式下都是1月8日
08-Jan-1999	任何模式下都是1月8日
99-Jan-08	在YMD模式下是1月8日，否则错误
08-Jan-99	1月8日，除了在YMD模式下是错误的之外
Jan-08-99	1月8日，除了在YMD模式下是错误的之外
19990108	ISO-8601; 任何模式下都是1999年1月8日
990108	ISO-8601; 任何模式下都是1999年1月8日

 

 

 

 

 

 

 

 

 

 

 

 

 

1.2 时间(time)：一天中的时刻，使用时、分、秒表示，其后面可以带有可选的时区，以下为合法的时间格式列表：

例子	描述
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
04:05:06 PST	带缩写的时区
2003-04-12 04:05:06 America/New_York	带全称的时区

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.3 时间戳(timestamp)：由一个日期和时间的联接组成，后面跟着一个可选的时区。如下两种方式都是有效的：

（1）1999-01-08 04:05:06

（2）1999-01-08 04:05:06 -8:00

1.4 时区：

例子	描述
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的缩写

 

 

 

 

 

 

 

 

 

关于时区需要注意的问题：

（1）SQL标准通过查看符号"+" 或 "-" 是否存在来区分常量的类型是 timestamp without time zone还是timestamp with time zone。 例如，TIMESTAMP '2004-10-19 10:23:54'的类型timestamp without time zone，TIMESTAMP '2004-10-19 10:23:54+02'的类型是timestamp with time zone。PostgreSQL不使用这个规则，因此前面的两个例子的例子都会被认为timestamp without time zone。在PostgreSQL中，timestamp without time zone类型的常量前面必须加上TIMESTAMP WITH TIME ZONE, 例如，TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'。    

（2）timestamp without time zone类型的常量中如果有时区信息，时区信息会被系统自动忽略。    

（3）timestamp with time zone类型的数据的内部存储的格式总是UTC（全球统一时间，以前也叫格林威治时间GMT）。如果一个输入值中指定了时区，系统将以该时区为依据将它转换为UTC格式，如果在输入的值中没有指定声明，系统以参数timezone的值作为指定时区为依据，将它转换为UTC格式。 

（4）如果要输出一个 timestamp with time zone类型的数据，它总是从 UTC被转到参数timezone指定的时区，并被显示为该时区的本地时间。 要看其它时区的该时间，要么修改 参数参数timezone的值，要么使用 AT TIME ZONE 子句。 

（5）在 timestamp without time zone 和 timestamp with time zone 之间的进行转换是通常假设 timestamp without time zone 数值的时区是参数timezone 指定的时区。可以用AT TIME ZONE 指定其它的时区。

1.5 时间间隔(interval)：（注：此部分理解还不是很好，有待完善）

interval类型的数值可以用下面语法来定义：

[@] quantity unit [quantity unit...] [direction]

这里quantity 是一个数字（可能有符号），unit 是 microsecond、 millisecond、second、minute,、hour、day、 week、month、year、decade、century、millennium或者这些单位的缩写或复数，direction 可以是 ago 或者为空。符号 “@” 是可选的，可以不写。

天、小时、分钟以及秒的数值的后面可以不用明确地跟单位。 比如，“1 12:59:10” 和 “1 day 12 hours 59 min 10 sec”是等价的。可选精度 p 的取值在0到6 之间，默认是输入的常量的精度。

2 PostgreSQL 中支持的日期/时间类型

PostgreSQL 支持 SQL标准中所有的日期和时间类型，如下所示：

名称	存储空间大小	描述	最小值	最大值	分辨率
date	4 bytes	只有日期	4713 BC(公元前4713)	5874897 AD(公历纪元)	1天
time [ (p) ] [ without time zone ]	8 bytes	只有时间	00:00:00	24:00:00	1微秒/14位
time [ (p) ] with time zone	12 bytes	只有时间，带时区	00:00:00+1459	24:00:00-1459	1微秒/14位
timestamp [ (p) ] [ without time zone ]	8 bytes	包括日期和时间	4713 BC	294276 AD	1微秒/14位
timestamp [ (p) ] with time zone	8 bytes	包括日期和时间，带时区	4713 BC	294276 AD	1微秒/14位
interval [ (p) ]	12 bytes	时间间隔	-178000000 年	178000000 年	1微秒/14位

 

 

 

 

 

 

 

 

 

 

 

 

参考资料：

https://www.cnblogs.com/IamThat/p/5111168.html

https://www.cnblogs.com/stephen-liu74/archive/2012/04/30/2293602.html

https://www.postgresql.org/docs/current/static/datatype-datetime.html

转载于:https://www.cnblogs.com/zwliang/p/9291095.html