8.5. 日期/时间类型

PostgreSQL 支持 SQL 中所有的日期和时间类型。 在 Table 8-9 中显示。 这些数据类型上可以进行的操作在 Section 9.9 描述。

Table 8-9. 日期/时间类型

名字存储空间描述最低值最高值分辨率
timestamp [ (p) ] [without time zone]8 字节包括日期和时间4713 BC5874897 AD1 毫秒 / 14 位
timestamp [ (p) ] with time zone8 字节日期和时间,带时区4713 BC5874897 AD1 毫秒 / 14 位
interval [ (p) ]12 字节时间间隔-178000000 年178000000 年1 毫秒
date4 字节只用于日期4713 BC32767 AD1 天
time [ (p) ] [ without time zone ]8 字节只用于一日内时间00:00:00.0023:59:59.991 毫秒
time [ (p) ] with time zone只用于一日内时间,带时区12 字节00:00:00.00+1223:59:59.99-121 毫秒

注意: PostgreSQL 7.3 以前,只写 timestamp 等效于 timestamp with time zone。 这样是为了和 SQL 兼容。

timetimestampinterval 接受一个可选的精度值 p,这个精度值声明在秒域后面小数点之后保留的位数。 缺省的时候在精度上是没有明确的绑定的, p 有用的范围对 timestampinterval 是从 0 到大约 6。

注意: 如果 timestamp 数值是以双精度浮点数(目前的缺省)的方式存储的, 那么精度的有效限制会小于 6。 timestamp 值是以 2000-01-01 午夜之前或之后以来的秒数存储的,而微秒的精度是为那些在 2000-01-01 前后几年的日期实现的, 对于那些远一些的日子,精度会下降。如果 timestamp 以八字节整数存储(一个编译时的选项),那么微秒的精度就可以在数值的全部范围内都可以获得。 不过,八位整数的时间戳的日期范围缩小到 4713 BC 到 294276 AD。

对于 time 类型,如果使用了八字节的整数存储,那么 p 允许的范围是从 0 到 6,如果使用的是浮点数存储,那么这个范围是 0 到 10。

类型time with time zone是 SQL 标准定义的, 但是整个定义有些方面会导致有问题的用法。在大多数情况下, datetimetimestamp without time zonetimestamp with time zone 的组合就应该能提供提供任何应用需要的日期/时间的完整功能。

类型 abstimereltime 是低分辨率类型,它们被用于系统内部。 我们不鼓励你在新的应用里面使用这些类型,同时我们支持合适的时候把旧应用中对应的类型转换成目前上面指明的。 因为这些旧类型的部分或全部可能会在未来的版本里消失。

8.5.1. 日期/时间输入

日期和时间的输入几乎可以是任何合理的格式,包括 ISO 8601SQL-兼容的, 传统 POSTGRES 的和其他的形式。 对于一些格式,日期输入里的月份和日子输入可能会让人模糊, 因此系统支持声明自己预期的这些字段的顺序。 把 DateStyle 参数设置为 MDY, 就是“月-日-年”的解析,设置为 DMY 就是 “日-月-年”,而 YMD 是 “年-月-日”。

PostgreSQL 在处理日期/时间输入上比 SQL 标准要求的更灵活。 参阅 Appendix B 获取关于日期/时间输入的准确的分析规则和可识别文本字段,包括月份,星期几,和时区。

请记住任何日期或者时间的文本输入需要由单引号包围, 就象一个文本字符串一样。 参考 Section 4.1.2.5 获取更多信息。SQL 要求下面的语法

type [ (p) ] 'value'

在这里可选的精度声明中的 p 是一个整数, 对应在秒域中小数部分的位数, 我们可以对 timetimestamp,和 interval 类型声明精度。 允许的精度在上面已经说明。如果在常量声明中没有声明精度,缺省是文本值的精度。

8.5.1.1. 日期

Table 8-10 显示了 date 类型可能的输入方式。

Table 8-10. 日期输入

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

8.5.1.2. 时间

当日时间类型是 time [ (p) ] without time zonetime [ (p) ] with time zone。 只写 time 等效于 time without time zone

这些类型的有效输入由当日时间后面跟着可选的时区组成。 (参阅 Table 8-11。) 如果在 time without time zone 类型的输入 中声明了时区,那么它会被无声地忽略。

Table 8-11. 时间输入

例子描述
04:05:06.789ISO 8601
04:05:06ISO 8601
04:05ISO 8601
040506ISO 8601
04:05 AM与 04:05 一样;AM 不影响数值
04:05 PM与 16:05一样;输入小时数必须 <= 12
04:05:06.789-8ISO 8601
04:05:06-08:00ISO 8601
04:05-08:00ISO 8601
040506-08ISO 8601
04:05:06 PST用名字声明的时区

Table 8-12. 时区输入

例子描述
PST太平洋标准时间(Pacific Standard Time)
-8:00ISO-8601 与 PST 的偏移
-800ISO-8601 与 PST 的偏移
-8ISO-8601 与 PST 的偏移
zulu军方对 UTC 的缩写(译注:可能是美军)
zzulu 的缩写

参考Appendix B 获取可以识别的时区输入。

8.5.1.3. 时间戳

时间戳类型的有效输入由一个日期和时间的联接组成,后面跟着一个可选的时区,一个可选的 AD 或者 BC。(另外,AD/BC 可以出现在时区前面,但这个顺序并非最佳的。) 因此

1999-01-08 04:05:06

1999-01-08 04:05:06 -8:00

是一个有效的数值, 它是兼容 ISO 8601 的。另外,下面这种使用广泛的格式

January 8 04:05:06 1999 PST

也受支持。

SQL 标准通过 "+" 或者 "-" 是否存在来区分 timestamp without time zonetimestamp 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 zonePostgreSQL 与标准不同的地方是要求必须明确键入 timestamp with time zone 文本:

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

。 如果一个文本不是明确地以 timestamp with time zone 开头,PostgreSQL 将不声不响忽略任何文本中指明的失去。 因此,生成的日期/时间值是从输入值的日期/时间字段衍生出来的,并且没有就时区进行调整。

对于 timestamp [without time zone],任何在输入中声明的时区都被悄悄吞掉。 也就是说,生成的日期/时间数值是从输入中明确的日期/时间字段中得出的,并且没有根据时区调整。

对于 timestamp with time zone,内部存储的数值总是 UTC (全球统一时间,以前也叫格林威治时间GMT)。如果一个输入值有明确的时区声明, 那么它将用该时区合适的偏移量转换成 UTC。如果在输入字串里没有时区声明, 那么它就假设是在系统的 timezone 参数里的那个时区,然后使用这个 timezone 时区转换成 UTC。

如果输出一个 timestamp with time zone,那么它总是从 UTC 转换成当前的 timezone 时区,并且显示为该时区的本地时间。 要看其它时区的该时间,要么修改 timezone,要么使用 AT TIME ZONE 构造(参阅 Section 9.9.3)。

timestamp without time zonetimestamp with time zone 之间的转换通常假设 timestamp without time zone 数值应该以 timezone 本地时间的形式接受或者写出。 其它的时区引用可以用 AT TIME ZONE 的方式为转换声明。

8.5.1.4. 间隔

interval数值可以用下面语法声明:

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

这里:quantity 是一个数字(可能有符号); unitsecond, 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 之间, 并且缺省是输入文本的精度。

8.5.1.5. 特殊值

PostgreSQL 为方便起见支持几个特殊输入值, 如在 Table 8-13 里面显示的那样。 值infinity-infinity 是特别在系统内部表示的,并且将按照同样的方式显示; 但是其它的都只是符号缩写,在读取的时候将被转换成普通的日期/时间值。 (特别是,now 和相关的字串在读取的时候就被转换成对应的数值。) 所有这些值在 SQL 命令里当作普通常量对待时,都需要写在单引号里面。

Table 8-13. 特殊日期/时间输入

输入字串有效类型描述
epochdate, timestamp1970-01-01 00:00:00+00 (Unix 系统零时)
infinitytimestamp比任何其它时间戳都晚
-infinitytimestamp比任何其它时间戳都早
nowdate, time, timestamp当前事务时间
todaydate, timestamp今日午夜
tomorrowdate, timestamp明日午夜
yesterdaydate, timestamp昨日午夜
allballstime00:00:00.00 UTC

下列 SQL 兼容函数也可以用于获取对应数据类型的当前时间值: CURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPLOCALTIMELOCALTIMESTAMP。最后四个接受一个可选的精度声明。 (见 Section 9.9.4 。) 不过,请注意这些 SQL 函数不是被当作数据输入串识别的。

8.5.2. 日期/时间输出

使用 SET DateStyle,时间/日期类型的输出格式可以设成四种风格之一: ISO 8601,SQL (Ingres),传统的 POSTGRES,和 German 。缺省是 ISO 格式。 (SQL 标准要求使用 ISO 8601 格式。"SQL" 输出格式的名字是历史偶然。)Table 8-14 显示了每种输出风格的例子。datetime 类型的 输出当然只是给出的例子里面的日期和时间部分。

Table 8-14. 日期/时间输出风格

风格描述描述例子
ISOISO-8601/SQL 标准1997-12-17 07:37:16-08
SQL传统风格12/17/1997 07:37:16.00 PST
POSTGRES原始风格Wed Dec 17 07:37:16 1997 PST
German地区风格17.12.1997 07:37:16.00 PST

如果声明了 DMY 字段,那么在 SQL 和 POSTGRES 风格里,日期在月份之前出现,否则月份出现在日期之前。 (参阅 Section 8.5.1 部分,看看这个设置是如何影响对输入值的解释。) Table 8-15 显示了一个例子。

Table 8-15. 日期顺序习惯

风格描述描述例子
SQL, DMY//17/12/1997 15:37:16.00 CET
SQL, MDY//12/17/1997 07:37:16.00 PST
Postgres, DMYday/month/yearWed 17 Dec 07:37:16 1997 PST

interval 的输出看起来象输入格式,只是象 centuryweek 这样的单位被转换成年和日,而 ago 被转换成合适的符号。在 ISO 模式下输出看起来象

[ quantity unit [ ... ] ] [ days ] [ hours:minutes:secondes ]

日期/时间风格可以由用户用 SET datestyle 命令 选取,或者在 postgresql.conf 配置文件里的参数 DateStyle 设置,或者服务器或客户端的 PGDATESTYLE 环境变量里设置。我们也可以用格式化函数 to_char(参阅 Section 9.8) 来更灵活地控制时间/日期地输出。

8.5.3. 时区

时区和时区习惯不仅仅受地球几何形状的影响,还受到政治决定的影响。 到了19世纪,全球的时区变得稍微标准化了些,但是还是易于遭受随意的修改, 部分是因为夏时制规则。 PostgreSQL 目前支持 1902 年到 2038 年之间的夏时制信息(对应于传统 Unix 系统时间的完整跨度)。 如果时间超过这个范围,那么假设时间是选取的时区的"标准时间",不管它们落在哪个年份里面。

PostgreSQL 在典型应用中尽可能与 SQL 的定义相兼容。但 SQL 标准在日期和时间类型和功能上有一些奇怪的混淆。两个显而易见的问题是:

为了克服这些困难,我们建议在使用时区的时候,使用那些同时包含日期和时间的日期/时间类型。 我们建议不要使用类型 time with time zone (尽管 PostgreSQL 出于合理应用以及为了与其他RDBMS实现兼容的考虑支持这个类型)。 PostgreSQL 假设你用于任何类型的本地时区都只包含日期或时间。

在系统内部,所有日期和时间都是用全球统一时间UTC格式存储, 时间在发给客户前端前由数据库服务器转换成本地时间,使用的是配置参数 timezone 声明的时区。

我们可以在 postgresql.conf 文件里设置配置参数 timezone, 或者用任何其它在 Section 16.4 描述的标准方法。 还有好几种特殊方法可以设置它:

请参考 Appendix B 获取一个可用时区的列表。

8.5.4. 内部

PostgreSQL 使用儒略历法用于所有日期/时间计算。 如果假设一年的长度是365.2425天时,这个方法可以 很精确地预计/计算从4713 BC(公元前4713年)到很久的未来的任意一天的日期。

19世纪以前的日期传统(历法)只是对一些趣味读物有意义, 而在我们这里好象没有充分的理由把它们编码入日期/时间控制器里面去。