Postgresql 中文操作指南

SQL 标准要求只写 timestamp 等同于 timestamp without time zone，PostgreSQL 遵守该行为。timestamptz 被接受为 timestamp with time zone 的缩写；这是 PostgreSQL 的扩展。

time、timestamp_和 _interval 接受可选的精度值 p，该值指定秒字段中保留的小数位数。默认情况下，对精度没有明确限制。p 的允许范围为 0 至 6。

interval 类型有一个附加选项，即通过编写以下短语之一来限制存储字段的集：

请注意，如果同时指定了 fields 和 p，则 fields 必须包括 SECOND，因为精度仅适用于秒。

类型 time with time zone 由 SQL 标准定义，但该定义展现出导致有用性存在疑问的属性。在大多数情况下，date、time、_timestamp without time zone_和 _timestamp with time zone_的组合应该可以提供任何应用程序所需的完整日期/时间功能范围。

日期和时间输入被接受为几乎任何合理的格式，包括 ISO 8601、SQL 兼容、传统 POSTGRES 等。对于某些格式，日期输入中日期、月份和年份的顺序是含糊不清的，支持指定这些字段的预期顺序。将 DateStyle 参数设置为 _MDY_以选择月-日-年解释，_DMY_以选择日-月-年解释，或 _YMD_以选择年-月-日解释。

PostgreSQL 在处理日期/时间输入方面，比 SQL 标准要求的更灵活。有关日期/时间输入的确切解析规则，以及包括月份、星期和时区在内的已识别文本字段，请参阅 Appendix B。

请记住，任何日期或时间文本输入都需要用单引号引起来，就像文本字符串一样。有关更多信息，请参阅 Section 4.1.2.7。SQL 要求以下语法：

其中 p 是可选的精度规范，提供秒字段中的小数位数。可以为 time、timestamp 和 interval 类型指定精度，范围为 0 到 6。如果在常量规范中未指定精度，则默认为字面值精度（但不多于 6 位数字）。

虽然输入字符串 now、today、tomorrow 和 yesterday 在交互式 SQL 命令中使用很合适，但当命令被保存以稍后再执行时，它们可能会有出人意料的行为，比如在已准备好的语句、视图和函数定义中。该字符串可被转换为特定的时间值，并继续用在它变得过时之后的很长一段时间。在这种情况下，改为使用其中一种 SQL 函数。例如，CURRENT_DATE + 1 比 'tomorrow'::date 更安全。

日期/时间类型的输出格式可以设置为四种格式之一：ISO 8601、SQL（Ingres）、传统的 POSTGRES（Unix 日期格式）或德语。默认格式是 ISO 格式。（SQL 标准要求使用 ISO 8601 格式。 “SQL” 输出格式的名称是一个历史巧合。） Table 8.14示出了每种输出格式的示例。_date_和 _time_类型的输出通常仅是日期部分或时间部分，与给定的示例一致。然而，POSTGRES 样式以 ISO 格式输出仅日期值。

Table 8.14. Date/Time Output Styles

ISO 8601 规定使用大写字母 T 分隔日期和时间。PostgreSQL 在输入中接受该格式，但在输出中使用空格而不是 T，如上所示。这是为了可读性和与 RFC 3339 及其他一些数据库系统的一致性。

在 SQL 和 POSTGRES 样式中，如果指定了 DMY 字段顺序，则 day 会出现在 month 之前，否则 month 会出现在 day 之前。（请参阅 Section 8.5.1，了解此设置如何影响输入值的解释。） Table 8.15显示示例。

Table 8.15. Date Order Conventions

在 ISO 样式中，时区始终显示为 UTC 的带符号数值偏移，格林威治以东的时区使用正号。如果偏移量是整数小时数，则会显示为 hh（仅小时）；如果偏移量是整数分钟数，则显示为 hh:_mm_；如果偏移量是整数秒数，则显示为 hh:_mm_:_ss_。（第三种情况在任何现代时区标准中都不可能出现，但当处理早于采用标准时区的时戳时，可能会出现。）在其他日期样式中，如果时区在当前区域中常用，则时区将显示为字母缩写。否则，它将显示为 ISO 8601 基本格式的带符号数值偏移（hh 或 hhmm）。

日期/时间样式可以选择使用 _SET datestyle_命令、_postgresql.conf_配置文件中的 DateStyle参数或服务器或客户端上的 _PGDATESTYLE_环境变量。

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

时区和时区约定受政治决策影响，而不仅仅是地球几何形状。世界各地的时区在20世纪已经得到了一些标准化，但仍然容易受到任意变化的影响，特别是在夏令时规则方面。PostgreSQL 使用广泛使用的 IANA（奥尔森）时区数据库来获取有关历史时区规则的信息。对于未来的时间，假设给定时区的最新已知规则将继续无限期地遵守。

PostgreSQL 努力与 SQL 标准定义保持兼容，以供典型用法。然而，SQL 标准对日期和时间类型和功能有一种奇怪的混合。两个明显的问题是：

为了解决这些困难，我们建议在使用时区时使用同时包含日期和时间的日期/时间类型。我们 not 建议使用 time with time zone 类型（尽管 PostgreSQL 出于旧式应用程序和遵守 SQL 标准的目的而支持它）。PostgreSQL 假设仅包含日期或时间的任何类型的本地时区。

所有支持时区的日期和时间都以 UTC 格式内部存储。它们在显示给客户端之前，会根据 TimeZone配置参数指定的时区转换为当地时间。

PostgreSQL 允许您以三种不同的形式指定时区：

简而言之，这就是缩写和全称之间的区别：缩写表示与 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 示例一样，这不一定是该日期的当地民用时间。

在所有情况下，时区名称和缩写都区分大小写。（这是与 8.2 之前的 PostgreSQL 版本相比的更改，后者在某些情况下区分大小写，而在其他情况下不区分大小写。）

时区名称或缩写都没有硬连接到服务器；它们是从安装目录的 _…​/share/timezone/_和 _…​/share/timezonesets/_下存储的配置文件中获取的（参见 Section B.4）。

TimeZone配置参数可以在文件 _postgresql.conf_中设置，或在 Chapter 20中描述的任何其他标准方式中设置。还有一些特殊的方式来设置它：

interval 值可以使用以下详细语法编写：

quantity_为数字（可能带有符号）；_unit_为 _microsecond、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 years 10 months' 相同。（实际上，这些较短的格式是 SQL 标准唯一允许的格式，并且在 IntervalStyle 设置为 sql_standard 时用于输出。）

间隔值也可以写为 ISO 8601 时间间隔，使用该标准 4.4.3.2 部分的“带有指示符的格式”或 4.4.3.3 部分的“备用格式”。带有指示符的格式如下所示：

字符串必须以 P_开头，并且可能包含引入一天中时间单位的 _T。 Table 8.16中给出了可用的单位缩写。可以省略单位，并且可以按任何顺序指定单位，但比一天小的单位必须出现在 _T_之后。特别是，_M_的含义取决于它是在 _T_之前还是之后。

Table 8.16. ISO 8601 Interval Unit Abbreviations

在备用格式中：

字符串必须以 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 解释。为了避免歧义，建议对任何负字段附加显式符号。

在内部，interval 值存储为三个整数字段：月、日和微秒。这些字段是分开的，因为一个月的日子数是可变的，而如果涉及夏令时过渡，一天可能有 23 或 25 小时。使用其他单位的间隔输入字符串已标准化为这种格式，然后以标准化方式重建以进行输出，例如：

这里，“7 天”为一周理解，已被分开，而较小和较大的时间单位已合并并标准化。

输入字段值可以有分数部分，例如 '1.5 weeks' 或 '01:02:03.45'。但是，因为 interval 内部仅存储整数字段，所以分数值必须转换为更小的单位。大于月份的单位的分数部分被舍入为一个月的整数，例如 '1.5 years' 变为 '1 year 6 mons'。星期和天数的分数部分计算为天数和微秒的整数，假设每月 30 天，每天 24 小时，例如 '1.75 months' 变为 1 mon 22 days 12:00:00。只有秒在输出时才会显示为分数。

Table 8.17显示了有效_interval_输入的一些示例。

Table 8.17. Interval Input

如前所述，PostgreSQL 将 interval 值存储为月、日和微秒。对于输出，月份字段通过除以 12 转换为年和月。天字段按原样显示。微秒字段转换为小时、分钟、秒和小数秒。因此，月、分和秒将永远不会显示为超过 0-11、0-59 和 0-59 的范围，而显示的年、天和小时字段可能很大。（如果需要将较大的天或小时值转置到下一个较高字段，可以使用 justify_days 和 justify_hours 函数。）

使用命令_SET intervalstyle_，间隔类型的输出格式可以设置成四种样式之一_sql_standardpostgrespostgres_verbose_或者_iso_8601_。默认是_postgres_样式。 Table 8.18显示了每种输出样式的示例。

如果区间值符合标准的限制（仅限年-月或仅限一天-时间，且不允许正负组件混合），则 sql_standard 格式生成符合 SQL 标准对区间字面值字符串的规范的输出。否则，输出看起来像一个标准的年-月字面值字符串，后接一个日-时字面值字符串，使用明确的符号消歧义混合符号区间。

_postgres_样式的输出匹配 DateStyle参数设置为_ISO_时8.4之前的PostgreSQL发布版本的输出。

postgres_verbose 格式的输出与在 DateStyle 参数设置为非 ISO 输出时，PostgreSQL 8.4 之前版本的输出相匹配。

iso_8601 格式的输出与 ISO 8601 标准的章节 4.4.3.2 中描述的“带指示符的格式”相匹配。

Table 8.18. Interval Output Style Examples