| PostgreSQL 9.5.3 中文手册 | |||
|---|---|---|---|
| 上一页 | 上一级 | 章 8. 数据类型 | 下一页 |
PostgreSQL支持SQL中所有的日期和时间类型，如表 8-9所示。这些数据类型上可用的操作如第 9.9 节所述。日期根据公历来计算，即使对于该历法被引入之前的年份也一样（见第 B.4 节）。
表 8-9. 日期/时间类型
| 名字 | 存储尺寸 | 描述 | 最小值 | 最大值 | 解析度 |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8字节 | 包括日期和时间（无时区） | 4713 BC | 294276 AD | 1毫秒 / 14位 |
| timestamp [ (p) ] with time zone | 8字节 | 包括日期和时间，有时区 | 4713 BC | 294276 AD | 1毫秒 / 14位 |
| date | 4字节 | 日期（没有一天中的时间） | 4713 BC | 5874897 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+1459 | 24:00:00-1459 | 1毫秒 / 14位 |
| interval [ fields ] [ (p) ] | 16字节 | 时间间隔 | -178000000年 | 178000000年 | 1毫秒 / 14位 |
注意: SQL要求只写timestamp等效于timestamp without time zone，并且PostgreSQL鼓励这种行为。timestamptz被接受为timestamp with time zone的一种简写，这是一种PostgreSQL的扩展。
time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界，p允许的范围对timestamp和interval类型是从 0 到 6。
注意: 当timestamp值被存储为八字节整数（目前是默认情况）时，在整个值的范围上微妙精度是可用的。当timestamp值被存储为双精度浮点数（一个已被启用的编译时选项）时，那么精度的有效限制会小于 6。timestamp值是以 2000-01-01 午夜之前或之后以来的秒数存储的。当timestamp值被用浮点数实现时，在2000-01-01前后几年的日期可以达到微秒的精度，但是对于远一些的日子，精度会下降。注意使用浮点日期时间允许显示比上文所述更大范围的timestamp值：从 4713 BC 到 5874897 AD。
同一个编译时选项也决定了time和interval值被存储为浮点数或八字节整数。在浮点数的情况中，当间隔的尺寸增长时，大interval值在精度上会下降。
对于time类型，如果使用了八字节的整数存储，允许的p的范围是从 0 到 6，如果使用的是浮点数存储，那么这个范围是 0 到 10。
interval类型有一个附加选项，它可以通过写下面之一的短语来限制存储的fields的集合：
YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE HOUR TO SECOND MINUTE TO SECOND
注意如果fields和p被指定，fields必须包括SECOND，因为精度只应用于秒。
类型time with time zone是 SQL 标准定义的，但是该定义显示出了一些会影响可用性的性质。在大多数情况下， date、time、timestamp without time zone和timestamp with time zone的组合就应该能提供任何应用所需的全范围的日期/时间功能。
类型abstime和reltime是低精度类型，它们被用于系统内部。 我们不鼓励你在应用里面使用这些类型，这些内部类型可能会在未来的版本里消失。
日期和时间的输入可以接受几乎任何合理的格式，包括 ISO 8601、SQL-兼容的、传统POSTGRES的和其他的形式。 对于一些格式，日期输入里的日、月和年的顺序会让人混淆， 并且支持指定所预期的这些域的顺序。把DateStyle参数设置为MDY，就是选择“月－日－年”的解释，设置为DMY就是 “日－月－年”，而YMD是 “年－月－日”。
PostgreSQL在处理日期/时间输入上比SQL标准要求的更灵活。 参阅附录 B获取关于日期/时间输入的准确的分析规则和可识别文本域，包括月份、星期几和时区。
请记住任何日期或者时间的文字输入需要由单引号包围，就象一个文本字符串一样。参考第 4.1.2.7 节获取更多信息。SQL要求下面的语法
type [ (p) ] 'value'
其中p是一个可选的精度声明，它给出了在秒域中的小数位数目。精度可以被指定给time、timestamp和interval类型。这允许前文所述的值。如果在一个常数声明中没有指定任何精度，它将默认取文字值的精度。
表 8-10显示了date类型可能的输入方式。
表 8-10. 日期输入
| 例子 | 描述 |
|---|---|
| 1999-01-08 | ISO 8601; 任何模式下的1月8日 （推荐格式） |
| January 8, 1999 | 在任何datestyle输入模式下都无歧义 |
| 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日 |
| 1999.008 | 年和一年中的日子 |
| J2451187 | 儒略日期 |
| January 8, 99 BC | 公元前99年 |
当日时间类型是time [ (p) ] without time zone和time [ (p) ] with time zone。 只写time等效于time without time zone。
这些类型的有效输入由当日时间后面跟着可选的时区组成（参阅表 8-11和表 8-12）。 如果在time without time zone的输入中指定了时区，那么它会被无声地忽略。你也可以指定一个日期但是它会被忽略，除非你使用了一个涉及到夏令时规则的时区，例如America/New_York。在这种情况下，为了判断是应用了标准时间还是夏令时时间，要求指定该日期。适当的时区偏移被记录在time with time zone值中。
表 8-11. 时间输入
| 例子 | 描述 |
|---|---|
| 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 | 全名指定的时区 |
表 8-12. 时区输入
| 例子 | 描述 |
|---|---|
| PST | 缩写（太平洋标准时间） |
| America/New_York | 完整时区名 |
| PST8PDT | POSIX风格的时区声明 |
| -8:00 | PST的ISO-8601偏移 |
| -800 | PST的ISO-8601偏移 |
| -8 | PST的ISO-8601偏移 |
| zulu | UTC的军方缩写 |
| z | zulu的短形式 |
参考第 8.5.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 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。因此要保证把上面的文字当作timestamp with time zone看待， 就要给它正确的显式类型：
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
如果一个文字已被确定是timestamp without time zone，PostgreSQL将不声不响忽略任何其中指出的时区。 即，结果值是从输入值的日期/时间域衍生出来的，并且没有就时区进行调整。
对于timestamp with time zone，内部存储的值总是 UTC （全球统一时间，以前也叫格林威治时间GMT）。如果一个输入值有明确的时区声明， 那么它将用该时区合适的偏移量转换成 UTC。如果在输入串里没有时区声明， 那么它就被假设是在系统的TimeZone参数里的那个时区，然后使用这个 timezone时区的偏移转换成 UTC。
如果一个timestamp with time zone值被输出，那么它总是从 UTC 转换成当前的timezone时区，并且显示为该时区的本地时间。要看其它时区的时间，要么修改timezone，要么使用AT TIME ZONE构造（参阅第 9.9.3 节）。
在timestamp without time zone和timestamp with time zone之间的转换通常假设timestamp without time zone值应该以timezone本地时间的形式接受或者写出。为该转换指定一个不同的可以用AT TIME ZONE。
为了方便，PostgreSQL支持一些特殊日期/时间输入值，如表 8-13所示。这些值中infinity和-infinity被在系统内部以特殊方式表示并且将被原封不动地显示。但是其他的仅仅只是概念上的速写，当被读到的时候会被转换为正常的日期/时间值（特殊地，now及相关串在被读到时立刻被转换到一个指定的时间值）。在作为常量在SQL命令中使用时，所有这些值需要被包括在单引号内。
表 8-13. 特殊日期/时间输入
| 输入串 | 合法类型 | 描述 |
|---|---|---|
| epoch | date, timestamp | 1970-01-01 00:00:00+00（Unix系统时间0） |
| infinity | date, timestamp | 比任何其他时间戳都晚 |
| -infinity | date, timestamp | 比任何其他时间戳都早 |
| now | date, time, timestamp | 当前事务的开始时间 |
| today | date, timestamp | 当日午夜 |
| tomorrow | date, timestamp | 明日午夜 |
| yesterday | date, timestamp | 昨日午夜 |
| allballs | time | 00:00:00.00 UTC |
下列SQL-兼容的函数可以被用来为相应的数据类型获得当前时间值： CURRENT_DATE、CURRENT_TIME、 CURRENT_TIMESTAMP、LOCALTIME、 LOCALTIMESTAMP。后四种接受一个可选的亚秒精度声明（参见第 9.9.4 节）。注意这些是SQL函数并且在数据输入串中不被识别。
时间/日期类型的输出格式可以设成四种风格之一： ISO 8601、SQL（Ingres）、传统的POSTGRES（Unix的date格式）或 German 。缺省是ISO格式（ISO标准要求使用 ISO 8601 格式。ISO输出格式的名字是历史偶然）。表 8-14显示了每种输出风格的例子。date和time类型的 输出通常只有日期或时间部分和例子中一致。不过，POSTGRES风格输出的是ISO格式的只有日期的值。
表 8-14. 日期/时间输出风格
| 风格声明 | 描述 | 例子 |
|---|---|---|
| ISO | ISO 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 |
注意: ISO 8601指定使用大写字母T来分隔日期和时间。PostgreSQL在输入上接受这种格式，但是在输出时它采用一个空格而不是T，如上所示。和一些其他数据库系统一样，这是为了可读性以及与RFC 3339的一致性。
SQL和POSTGRES风格中，如果DMY域顺序被指定，“日”将出现在“月”之前，否则“月”出现在“日”之前（有关该设置如何影响输入值的解释，请参考第 8.5.1 节）。表 8-15给出了例子。
表 8-15. 日期顺序习惯
| datestyle设置 | 输入顺序 | 例子输出 |
|---|---|---|
| SQL, DMY | 日/月/年 | 17/12/1997 15:37:16.00 CET |
| SQL, MDY | 月/日/年 | 12/17/1997 07:37:16.00 PST |
| Postgres, DMY | 日/月/年 | Wed 17 Dec 07:37:16 1997 PST |
日期/时间风格可以由用户使用SET datestyle命令选取，在postgresql.conf配置文件里的参数DateStyle设置或者在服务器或客户端的PGDATESTYLE环境变量里设置。
格式化函数to_char（见第 9.8 节）也可以作为一个更灵活的方式来格式化日期/时间输出。
时区和时区习惯不仅仅受地球几何形状的影响，还受到政治决定的影响。 到了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视图中（参见第 49.78 节）。PostgreSQL用广泛使用的 IANA 时区数据来实现该目的，因此相同的时区名字也可以在很多其他软件中被识别。
一个时区缩写，例如PST。这样一种声明仅仅定义了到UTC的一个特定偏移，而不像完整时区名那样指出整套夏令时转换日期规则。能被识别的缩写被列在pg_timezone_abbrevs视图中（参见第 49.77 节）。你不能将配置参数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中被设置，或者使用第 18 章中描述的任何一种标准方法设置。同时也有一些特殊的方法来设置它：
SQL命令SET TIME ZONE为会话设置时区。它是SET TIMEZONE TO的另一种拼写，它更加符合SQL的语法。
libpq客户端使用PGTZ环境变量来通过连接发送一个SET TIME ZONE命令给服务器。
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输入的例子。
间隔类型的输出格式可以被设置为四种风格之一：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小节中描述的"带标志符的格式"。