8.5. 日期/时间类型

8.5.1. 日期/时间输入

日期和时间的输入几乎可以是任何合理的格式，包括 ISO-8601 格式、 SQL -兼容格式、 传统 POSTGRES 格式、其它的形式。对于一些格式， 日期输入里的日、月、年可能会让人迷惑，因此系统支持自定义这些字段的顺序。 把 DateStyle 参数设置为 MDY 就按照"月-日-年"解析， 设置为 DMY 就按照"日-月-年"解析，设置为 YMD 就按照"年-月-日"解析。

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

请记住任何日期或者时间的文本输入需要由单引号包围，就像一个文本字符串一样。 参考 第 4.1.2.7 节 获取更多信息。 SQL 要求使用下面的语法：

type [ (p) ] 'value'

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

1999-01-08 04:05:06

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

January 8 04:05:06 1999 PST

TIMESTAMP '2004-10-19 10:23:54'

TIMESTAMP '2004-10-19 10:23:54+02'

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

8.5.2. 日期/时间输出

日期/时间类型的输出格式可以设成 ISO 8601(默认)、 SQL (Ingres)、 传统的 POSTGRES (Unix date 格式)或German四种风格之一。 SQL 标准要求使用 ISO 8601 格式。 "SQL" 输出格式的名字是历史偶然。 表 8-14 显示了每种输出风格的例子。 date 和 time 类型的输出当然只是给出的例子里面的日期和时间部分。

如果声明了 DMY 顺序，那么在 SQL 和 POSTGRES 风格里， 日期在月份之前出现，否则月份出现在日期之前(参阅 第 8.5.1 节 看看这个设置如何影响对输入值的解释)。 表 8-15 显示了一个例子。

用户可以用 SET datestyle 命令选取日期/时间的风格， 也可以在配置文件 postgresql.conf 中的 DateStyle 参数中设置，或者在服务器或客户端的 PGDATESTYLE 环境变量中设置。

我们也可以用格式化函数 to_char (参见 第 9.8 节 ) 来更灵活地控制时间/日期地输出。

8.5.3. 时区

时区和时区习惯不仅仅受地球几何形状的影响，还受到政治决定的影响。 到了 19 世纪，全球的时区变得稍微标准化了些，但是还是易于遭受随意的修改， 部分是因为夏时制规则。 PostgreSQL 使用广泛使用的 zoneinfo （Olson）时区信息数据库有关历史时区的规则。 对于未来的时间，假设对于给定时区最近的规则将在未来继续无期限的遵守。

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

• date 类型与时区没有联系，而 time 类型却有或可以有。 然而，现实世界的时区只有在与时间和日期都关联时才有意义， 因为时间偏移量(时差)可能因为实行类似夏时制这样的制度而在一年里有所变化。

• 缺省的时区用一个数字常量表示与 UTC 的偏移(时差)。因此， 当跨 DST (夏时制)界限做日期/时间算术时， 我们根本不可能把夏时制这样的因素计算进去。

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

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

PostgreSQL 允许你用三种方法指定时区：

• 完整的时区名。例如 America/New_York 。所有可以识别的时区名在 pg_timezone_names 视图中列出(参见 第 47.71 节 )。 PostgreSQL 使用广泛使用的 zoneinfo 时区数据， 所以这些时区名在其它软件里也能被轻松的识别。

• 时区缩写。例如 PST 。这种缩写名通常只是定义了相对于 UTC 的偏移量， 而前一种完整的时区名可能还隐含着一组夏时制转换规则。 所有可以识别的时区缩写在 pg_timezone_abbrevs 视图中列出(参见 第 47.70 节 )。你不能设置 TimeZone 或 log_timezone 配置参数为时区缩写，但是你可以在日期/时间输入值中结合 AT TIME ZONE 操作符使用时区缩写。

• 除完整的时区名及其缩写之外， PostgreSQL 还接受 POSIX 风格的 STD offset 或 STD offset DST 格式的时区，其中的 STD 是时区缩写、 offset 是一个相对于 UTC 的小时偏移量、 DST 是一个可选的夏时制时区缩写 (假定相对于给定的偏移量提前一小时)。例如，如果 EST5EDT 不是一个已识别的时区名， 那么它将等同于美国东部时间。如果存在夏时制时区名是当前时区名，根据 zoneinfo 时区数据库的 posixrules 条目中相同的夏时制事务规则，可以考虑使用这个特性。 在一个 PostgreSQL 标准安装中， posixrules 与 US/Eastern 相同，因此POSIX格式的时区声明遵循USA夏时制规则。如果需要，可以通过替换 posixrules 文件来调整该习惯。

简言之，这就是完整的时区名与时区缩写之间的差异：时区缩写总是代表一个相对于 UTC 的固定偏移量， 然而大多数完整的时区名隐含着一个本地夏令时规则，因此就有可能有两个相对于 UTC 的不同偏移量。

需要警惕的是，由于没有合理的时区缩写检查，POSIX格式的时区特点能导致静默的伪输入。 例如，使用 SET TIMEZONE TO FOOBAR0 时，实际上系统使用的是一个很特别的UTC缩写。 另一个需要注意的是，在POSIX时区名中，积极的偏移用于 west 格林尼治位置。 在其他地方， PostgreSQL 遵循ISO-8601规定， 即积极的时区偏移 east 格林威治。

总体而言， PostgreSQL 8.2 版本以后时区名在所有情况下都是大小写无关的。 而之前的版本在某些情况下是大小写敏感的。

无论是完整的时区名还是时区缩写都不是硬连接进服务器的，它们都是从安装目录下的 .../share/timezone/ 和 .../share/timezonesets/ 配置文件中获取的(参见 第 B.3 节 )。

可以在 postgresql.conf 文件里设置 TimeZone 配置参数，或者用任何其它在 第 18 章 描述的标准方法。 除此之外，还有好几种特殊方法可以设置它：

• 使用 SQL 命令 SET TIME ZONE 为会话设置时区， 这是 SET TIMEZONE TO 的一个可选的拼写方式，更加兼容标准。

• 如果在客户端设置了 PGTZ 环境变量，那么 libpq 在连接时将使用这个环境变量给后端发送一个 SET TIME ZONE 命令。

8.5.4. 间隔输入

interval 类型值可以用下面的详细语法写：

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

这里 quantity 是一个数字（可能已标记）； unit 可以是 microsecond ， millisecond ， second ， minute ， hour ， day ， week ， month ， year ， decade ， century ， millennium 或这些单位的缩写或复数。 direction 可以是 ago 或为空。 @ 标记是可选的。不同的单位的数量被隐式地添加适当的计算符号。 ago 否定所有。如果 IntervalStyle 设置为 postgres_verbose ，那么这个语法同样用于间隔输出。

可以在没有明确单位标记的情况下声明天，小时，分钟和秒。例如， '1 12:59:10' 等同于 '1 day 12 hours 59 min 10 sec' 。同样， 可以用一个破折号来声明一个年和月的组合，例如 '200-10' 等同于 '200 years 10 months' 。（事实上， SQL 标准值允许短的格式， 并且当 IntervalStyle 设置为 sql_standard 时，用于输出）。

要么使用4.4.3.2的 "format with designators" ，要么使用4.4.3.3的 "alternative format" ，间隔值可以写为ISO 8601的时间间隔。格式如下：

P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

字符串必须以 P 开始，并且可以含有一个 T 用以指明一天中时间的格式。 可用单位的缩写在 表 8-16 有说明。可以忽略单位， 也可以以任意顺序声明，但单位小于一天时必须在 T 之后。 尤其 M 的含义依赖于它在 T 之前或之后。

以缩写格式：

P [ years-months-days ] [ T hours:minutes:seconds ]

一个字符串必须以 P 开始，然后以 T 隔开日期和时间。 给出的值是如同ISO 8601日期的数字。

当用 fields 规范写一个时间间隔常数，或将一个字符串标记为用 fields 规范定义的一个间隔列时，未标记单位的解释由 fields 解释。如 INTERVAL '1' YEAR 读作1年，然而 INTERVAL '1' 代表1秒。 同样， fields 规范中 "最低" 有效字段值规定会被静默的忽略。 如， INTERVAL '1 day 2:03:04' HOUR TO MINUTE 会导致删除秒字段，而不是天字段。

根据 SQL 标准，间隔值的所有字段必须有相同的符号，因此前导负号可以用于所有字段； 如 '-1 2:03:04' 中负号同时应用于天和小时/分钟/秒。 PostgreSQL 允许字段有不同的标记，并且传统上，文本表述中的每个字段会被认为是独立标记的， 因此在这个例子中的小时/分钟/秒被认为是正值。如果 IntervalStyle 被设置为 sql_standard ，那么前导标记被认为是应用于所有字段的 （当然前提是没有再出现其他标记），否则会使用传统的 PostgreSQL 解释。 为了避免这种歧义，如果任何字段是负的，建议为每个字段附上一个明确的标记。

PostgreSQL 内部， interval 值被存储为月，日，秒的格式， 这是因为月中包含天数不同，并且如果进行了夏令时调整，那么一天可以有23或25小时。 当秒字段可以存储分数时，月和天字段可以是整数型。 由于时间间隔通常是由常量字符串或 timestamp 减法来定义的， 这种存储方法在大多数情况下很有效。 justify_days 和 justify_hours 函数可用于调整溢出正常范围值的天和小时。

在详细的输入格式，以及更紧凑的输入格式中，字段值可以有小数部分， 例如 '1.5 week' 或 '01:02:03.45' 。这种输入被转换成恰当的月， 天和秒来存储。由于这样会产生小数的月或天，因此在低阶字段中引入了分数， 使用1 month = 30 days 和 1 day = 24 hours的转换。例如， '1.5 month' 即1个月15天。输出中，只有秒可以写成分数形式。

表 8-17 中有一些有效的 interval 输入的例子。

8.5.5. 间隔输出

间隔类型的输出格式可以用命令 SET intervalstyle 设置为下面四种类型： sql_standard ， postgres ， postgres_verbose 或 iso_8601 。默认是 postgres 格式， 表 8-18 中有每种格式的示例。

sql_standard 格式产生的输出结果符合SQL的间隔字符串标准， 如果间隔值满足标准的限制（无论只有年-月，或只有天-时间，没有积极和消极的构成的混合）。 否则输出类似一个标准年-月文本字符串后跟有一个天-时间文本字符串， 带有添加明确标记的消除歧义混合信号的时间间隔。

当参数 DateStyle 设置为 ISO 时， postgres 格式的输出与 PostgreSQL 8.4之前的版本一致。

当参数 DateStyle 设置为非- ISO ， postgres_verbose 格式的输出与 PostgreSQL 8.4之前的版本一致。

iso_8601 格式的输出与ISO 8601标准4.4.3.2节中的 "format with designators" 一致。