日期/时间类型

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 。缺省是ISO格式（ISO标准要求使用 ISO 8601 格式。ISO输出格式的名字是历史偶然）。表 8-14显示了每种输出风格的例子。date和time类型的 输出通常只有日期或时间部分和例子中一致。不过，POSTGRES风格输出的是ISO格式的只有日期的值。

SQL和POSTGRES风格中，如果DMY域顺序被指定，“日”将出现在“月”之前，否则“月”出现在“日”之前（有关该设置如何影响输入值的解释，请参考第 8.5.1 节）。表 8-15给出了例子。

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

格式化函数to_char（见第 9.8 节）也可以作为一个更灵活的方式来格式化日期/时间输出。

8.5.3. 时区

时区和时区习惯不仅仅受地球几何形状的影响，还受到政治决定的影响。 到了19世纪，全球的时区变得稍微标准化了些，但是还是易于遭受随意的修改，部分是因为夏时制规 则。PostgreSQL使用广泛使用的 IANA (Olson) 时区数据库来得到有关历史时区规则的信息。对于未来的时间，我们假设关于一个给定时区的最新已知 规则将会一直持续到无穷远的未来。

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

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

• 缺省的时区会指定一个到UTC的数字常量偏移（时差）。因此，当跨DST边界做日期/时间算术时， 我们根本不可能适应于夏时制时间。

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

在系统内部，所有时区相关的日期和时间都用UTC存储。它们在被显示给客户端之前会被转换成由TimeZone配置参数指定的本地时间。

PostgreSQL允许你使用三种不同形式指定时区：

• 一个完整的时区名字，例如America/New_York。能被识别的时区名字被列在pg_timezone_names视图中（参见第 50.80 节）。PostgreSQL用广泛使用的 IANA 时区数据来实现该目的，因此相同的时区名字也可以在很多其他软件中被识别。

• 一个时区缩写，例如PST。这样一种声明仅仅定义了到UTC的一个特定偏移，而不像完整时区名那样指出整套夏令时转换日期规则。能被识别的缩写被列在pg_timezone_abbrevs视图中（参见第 50.79 节）。你不能将配置参数TimeZone或log_timezone设置成一个时区缩写，但是你可以在日期/时间输入值和AT TIME ZONE操作符中使用时区缩写。

• 除了时区名和缩写，PostgreSQL将接受POSIX-风格的 时区声明，形式为STDoffset或 STDoffsetDST， 其中STD是一个区域缩写、offset是从UTC西 起的以小时计的数字偏移量、DST是一个可选的夏令时区域缩 写（被假定为给定偏移量提前一小时）。例如，如果EST5EDT还不是一 个被识别的区域名，它可以被接受并且可能和美国东海岸时间的功效相同。在这种语法中， 一个时区缩写可以是一个字母的字符串或者由尖括号（<>）包围 的任意字符串。当一个夏令时区域缩写出现时，会假定根据 IANA 时区数据库的 posixrules条目中使用的同一个夏令时转换规则使用它。 在一个标准的PostgreSQL安装中， posixrules和US/Eastern相同， 因此POSIX-风格的时区声明遵循美国的夏令时规则。如果需要，你可以通过替换 posixrules文件来调整这种行为。

简而言之，在缩写和全称之间是有不同的：缩写表示从UTC开始的一个特定偏移量， 而很多全称表示一个本地夏令时规则并且因此具有两种可能的UTC偏移量。例如， 2014-06-04 12:00 America/New_York表示纽约本地时间的中午， 这个特殊的日期是东部夏令时间（UTC-4）。因此2014-06-04 12:00 EDT 指定的是同一个时间点。但是2014-06-04 12:00 EST指定东部标准时间的 中午（UTC-5），不管在那个日期夏令时是否生效。

更要命的是，某些行政区已经使用相同的时区缩写在不同的时间表示不同的 UTC 偏移量。例如， 在莫斯科MSK在某些年份表示 UTC+3 而在另一些年份表示 UTC+4。 PostgreSQL 会根据在指定的日期它们到底表示什么（或者最近表示什么） 来解释这种缩写。但是，正如上面的EST例子所示，这并不是必须和那一天的本地 标准时间相同。

你应该注意到POSIX-风格的时区特性可能导致伪造的输入被接受，因为它没有对区域缩写合理性的检查。例如SET TIMEZONE TO FOOBAR0将会正常工作，让系统实际使用一个相当奇怪的UTC缩写。另一个需要记住的问题是在POSIX时区名中，正值的偏移量被用于格林威治以西的位置。在其他情况下，PostgreSQL将遵循 ISO-8601 惯例，认为正值的时区偏移量是格林威治以东。

在所有情况下，时区名及其缩写都是大小写不敏感的（这是对PostgreSQL 8.2之前版本的一个修改，在这些版本中某些环境下时区名是大小写敏感的而在另外一些环境中却是大小写不敏感的）。

时区名和缩写都不是硬写在服务器中的，它们是从存储在安装目录下的.../share/timezone/和.../share/timezonesets/子目录中获取的（参见第 B.3 节）。

TimeZone配置参数可以在文件postgresql.conf中被设置，或者使用第 19 章中描述的任何一种标准方法设置。同时也有一些特殊的方法来设置它：

• SQL命令SET TIME ZONE为会话设置时区。它是SET TIMEZONE TO的另一种拼写，它更加符合SQL的语法。

• libpq客户端使用PGTZ环境变量来通过连接发送一个SET TIME ZONE命令给服务器。

8.5.4. 间隔输入

interval值可以使用下列语法书写：

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

其中quantity是一个数字（很可能是有符号的）； unit是毫秒、 millisecond、second、 minute、hour、day、 week、month、year、 decade、century、millennium 或者缩写或者这些单位的复数； direction可以是ago或者为空。At符号（@）是一个可选的噪声。不同单位的数量通过合适的符号计数被隐式地添加。ago对所有域求反。如果IntervalStyle被设置为postgres_verbose，该语法也被用于间隔输出。

日、小时、分钟和秒的数量可以不适用显式的单位标记指定。例如，'1 12:59:10'被读作'1 day 12 hours 59 min 10 sec'。同样，一个年和月的组合可以使用一个横线指定，例如'200-10'被读作'200年10个月'（这些较短的形式事实上是SQL标准唯一许可的形式，并且在IntervalStyle被设置为sql_standard时用于输出）。

间隔值也可以被写成 ISO 8601 时间间隔，使用该标准4.4.3.2小节的"带标志符的格式"或者4.4.3.3小节的"替代格式"。带标志符的格式看起来像这样：

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日期的数字给出。

在用一个域声明书写一个间隔常量时，或者为一个用域声明定义的间隔列赋予一个串时，对于为标记的量的解释依赖于域。例如INTERVAL '1' YEAR被解读成1年，而INTERVAL '1'表示1秒。同样，域声明允许的最后一个有效域"右边"的域值会被无声地丢弃掉。例如书写INTERVAL '1 day 2:03:04' HOUR TO MINUTE将会导致丢弃秒域，而不是日域。

根据SQL标准，一个间隔值的所有域都必须由相同的符号，这样一个领头的负号将会应用到所有域；例如在间隔文字'-1 2:03:04'中的负号会被应用于日、小时、分钟和秒部分。PostgreSQL允许域具有不同的符号，并且在习惯上认为以文本表示的每个域具有独立的符号，因此在这个例子中小时、分钟和秒部分被认为是正值。如果IntervalStyle被设置为sql_standard，则一个领头的符号将被认为是应用于所有域（但是仅当没有额外符号出现）。否则将使用传统的PostgreSQL解释。为了避免混淆，我们推荐在任何域为负值时为每一个域都附加一个显式的符号。

内部的interval值被存储为月、日和秒。这是因为一个月中的天数是变化的，并且在涉及到夏令时调整时一天可以有23或25小时。月和日域是整数，而秒域可以存储分数。因为间隔通常都是从常数字符串或timestamp减法创建而来，这种存储方法在大部分情况都工作良好。函数justify_days和justify_hours可用于调整超过其常见范围的日数和小时数。

在冗长的输入格式中，以及在更紧凑输入格式的某些域中，域值可以有分数部分；例如'1.5 week'或'01:02:03.45'。这样的输入被转换为合适的月数、日数和秒数用于存储。当这样会导致月和日中的分数时，分数被加到低序域中，使用的转换因子是1月=30日和1日=24小时。例如，'1.5 month'会变成1月和15日。只有秒总是在输出时被显示为分数。

表 8-17展示了一些有效interval输入的例子。

8.5.5. 间隔输出

间隔类型的输出格式可以被设置为四种风格之一：sql_standard、postgres、postgres_verbose或iso_8601，设置方法使用SET intervalstyle命令。默认值为postgres格式。表 8-18展示了每种输出风格的例子。

如果间隔值符合SQL标准的限制（仅年-月或仅日-时间，没有正负值部分的混合），sql_standard风格为间隔文字串产生符合SQL标准规范的输出。否则输出将看起来像一个标准的年-月文字串跟着一个日-时间文字串，并且带有显式添加的符号以区分混合符号的间隔。

当DateStyle参数被设置为ISO时，postgres风格的输出匹配PostgreSQL 8.4版本以前的输出。

当DateStyle参数被设置为非ISO输出时，postgres_verbose风格的输出匹配PostgreSQL 8.4版本以前的输出。

iso_8601风格的输出匹配在ISO 8601标准的4.4.3.2小节中描述的"带标志符的格式"。