日期/时间类型

Greenplum 完整支持全部 SQL 日期和时间类型, 参见 Table 1。对于这些数据类型可用的操作说明, 参见 PostgreSQL 文档 日期/时间函数和运算符。 日期根据罗马日历计算，在日历引入之前的年也这样计算 (参见 PostgreSQL 文档 日期单位的历史了解更多信息)。

Note: SQL 标准要求 timestamp 与 timestamp without time zone 写法一致, Greenplum 实现了这一点。 timestamptz 是 timestamp with time zone 的缩写; 这是 PostgreSQL 的一点扩展。

time, timestamp, 和 interval 接受一个可选的精度值 p ，来指定秒的小数部分数字。默认情况下, 没有明确精度指定。对于 timestamp 和 interval 类型, 允许的 p 值范围为 0 到 6。

Note: 当 timestamp 值按八字节整数存储(当前默认), 精度可全范围(0 到 6 位)任意设置。当 timestamp 值按双精度浮点数存储(一个过时的编译选项), 精度有效值可能小于 6 位。 timestamp 值存储的是从 2000-01-01 午夜开始的秒的数值。 当 timestamp 值按浮点数实现时，2000-01-01 附近的一些年可以达到微秒精度，但是距离这个日期稍远的一些时间精度就会有所降低。注意：浮点存储的 timestamp 值允许更大的日期范围，比上表中给出的要大，即可以表示从 4713 BC 到 5874897 AD 的日期。

同样的编译选项也决定 time 和 interval 类型值存储为浮点数还是八字节整数。 当存储位浮点数的时候, 当时间间隔增大时， interval 的精度有所降低。

对于 time 类型, 使用八字节整数存储时，允许的 p 值范围从 0 到 6；使用浮点数存储时，允许的 p 值范围从 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, 因为精度参数 p 只对秒起作用。

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 指定 年-月-日 格式。

Greenplum 在处理日期/时间输入上比 SQL 标准要求的更加弹性。参见 PostgreSQL 文档 附录 B. 时间/日期支持，了解时间/日期输入的确切解析规则，以及如何识别文本字段，包括月份, 星期和时区。

记住，任何日期或时间字面值需要用单引号引起来, 就像字符串一样. SQL 需要这样的语法

type [ ( p ) ] ' value '

这里， p 是一个可选精度参数，指定秒的小数位数。它对以下类型起作用， time, timestamp, 以及 interval 类型。 允许设定的值范围已经在上面文档中说明； 如果没有指定精度，默认为字面值的精度。

日期

Table 2 一些可能的 date 类型输入格式.

时间

时间类型包括 time [ ( p ) ] without time zone 和 time [ ( p ) ] with time zone。 time 与 time without time zone 等价.

这些类型的有效的输入格式由时间加上一个可选的时区. (参见 Table 3 和 Table 4.) 如果在输入中给 time without time zone 类型指定了时区, 则时区会被忽略。你如果指定了一个日期，也会被忽略，除非你用了包含夏令时的时区，例如 America/New_York. 这种情况下，指定日期是必要的，因为需要决定当前是否是标准时间，还是夏令时时间. 使用 time with time zone 类型时，合适的时区偏移会被记录.

参考 时区 了解更多时区格式输入信息.

时间戳

时间戳的有效输入格式由以下几个部分组成：日期，时间，时区(可选), 接着可选的 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 类型的字面值. Greenplum 在确定类型之前从不检查字面值内容, 所以上面两个字面值都会被认为是 timestamp without time zone 类型. 为确保当作 timestamp with time zone 类型对待, 请像这样给出明确类型: TIMESTAMP WITH TIME ZONE ‘2004-10-19 10:23:54+02’ 对于一个已经确定为 timestamp without time zone 类型的字面值, Greenplum 会抛弃时区相关信息. 也就是说, 结果时间戳仅仅根据日期/时间确定，不会根据时区进行调整.

对于 timestamp with time zone 类型, 内部值总是按照 UTC (统一协调时间, 通常叫做格林威治时间, GMT) 存储. 带有明确时区的输入值会使用合适的时区偏移转为 UTC 时间。 如果输入字符串中没有指定时区，会假定为当前系统的 TimeZone 参数, 并使用这个参数的偏移转换为 UTC 时间。

当输出一个 timestamp with time zone 类型值, 总是根据当前 timezone 时区值进行转换, 并显示为本地时间. 要想以另一个时区查看时间，要么改变 timezone 设置，要么使用 AT TIME ZONE 构造 (参见 PostgreSQL 文档的AT TIME ZONE).

在 timestamp without time zone 与 timestamp with time zone 间转换通常假定 timestamp without time zone 中的值应该是以 timezone 为时区的本地时间. 当然，也可以用 AT TIME ZONE 指定一个不同的时区.

特殊值

为了方便, Greenplum 支持几种特殊的日期/时间输入值格式, 参见 Table 5. infinity 和 -infinity 值是用于系统内部的特殊表示，会被直接显示; 但其他都只是些快捷记号，会在读取的时候被转换为常规的日期/时间值.(特别地, now 和相关字符串会被转换为读取时的当前特定时间值.) 所有这些常量值在 SQL 命令中都需要用单引号引起来.

下面这些 SQL-兼容函数也可以用来获取相应类型的当前时间: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. 后面四个可接受一个可选的亚秒级精度参数. (参见 PostgreSQL 文档的当前日期/时间.) 注意 有一些 SQL 函数在输入字符串中 不 被接受.

日期/时间输出

日期/时间类型的输出格式可以被设置为以下四种样式: ISO 8601, SQL (Ingres), 传统 POSTGRES (Unix date 格式), 或者 German. 默认是 ISO 格式. (SQL 标准需要使用 ISO 8601 格式. SQL 输出格式这个名字是由于偶然的历史原因.) Table 6 给出了各种输出格式的例子. date 和 time 类型的输出格式通常为对应例子中的日期或时间部分. 然而, POSTGRES 格式的纯日期输出用的是 ISO 格式.

Note: ISO 8601 规定使用大写字母 T 隔开日期和时间. Greenplum 支持这个输入格式, 但是输出时使用空格而不是 T, 上面您已经看到了. 这是为了可读性，同时与 RFC 3339 以及一些其他数据库系统保持一致.

在 SQL 和 POSTGRES 样式中, 如果指定 DMY 模式，日会出现在月之前; 其他情况下月在日之前. (参见 Table 2 关于这些设置如何影响输入值解释.) Table 7 中的例子.

用户可以使用以下方式选择日期/时间样式: SET datestyle 命令, postgresql.conf 配置文件中的 DateStyle 参数, 或者服务器或客户端的 PGDATESTYLE 环境变量.

格式化函数 to_char (参见 数据类型格式化函数) 是一种格式化日期/时间输出的更加灵活的方式.

时区

时区和时区转换，不仅仅是地理位置问题，也受政治决定影响. 从 1900 年以来，世界上的时区稍微标准了一点, 但还是在持续的改变, 尤其是夏令时规则. Greenplum 使用广泛采纳的 IANA (Olson) 时区数据库来定义历史时区规则. 对于未来时间, 假定给定时区的现行已知规则会无限期持续.

Greenplum 保持与 SQL 标准兼容的典型用法定义. 然而, SQL 标准偶尔也有一点混淆时间和日期类型和能力. 两个明显的问题是:

1. 虽然 date 类型不能带时区, 但是 time 类型是可以的. 不与日期和时间关联，时区在真实世界意义不大, 因为偏移变化与夏令时边界相关.
2. 默认时区是与 UTC 之间差异的常量数值. 因而，当进行跨 DST 边界的日期/时间数值计算时，可能采用夏令时.

为了解决这些困难, 当使用时区的时候，我们推荐用同时包含日期和时间的日期/时间类型. 我们 不 推荐使用 time with time zone 类型(虽然 Greenplum 支持，但主要是为了一个老应用，同时保持与 SQL 标准兼容). 对于任何只包含日期或时间的类型, Greenplum 假定是你的本地时区(的时间或日期).

所有时区感知的日期和时间内部都存储为 UTC 值. 当被显示到客户端时，才通过 TimeZone 配置参数的值转换为指定时区的日期或时间.

Greenplum 允许你以三种不同的形式指定时区:

1. 时区全称, 例如 America/New_York. 在 pg_timezone_names 视图里列出了所有可被识别的时区名称. 为了这个目的，Greenplum 使用广泛采纳的 IANA 的时区数据, 因此同样的时区名称也可以被很多其他软件识别.
2. 另一个形式时时区缩写, 例如 PST. 对于 时区全称，能够暗示夏令时规则，而时区缩写这个规范只能表示与 UTC 直接的时区偏移. 可识别的缩写列在 pg_timezone_abbrevs 视图中. 你不能给时区缩写设置配置参数 TimeZone 或 log_timezone, 但你可以在时间/日期输入值中通过 AT TIME ZONE 操作符使用缩写.
3. 除了时区全称和时区缩写外, Greenplum 也接受 POSIX-风格的时区规范，形如 STD offset 或者 STD offset DST, 这里 STD 是一个时区缩写, offset 是 UTC 往西偏移的小时数值, DST 是一个可选的夏令时时区缩写, 假定为比给定偏移提前一个小时. 举个例子, 如果 EST5EDT 还没有被识别为时区名称, 它能被接受，相当于美国东岸时间(United States East Coast time). 在这种语法中, 时区缩写可以是一串字母, 或者包围在尖括号中的任意字符串( <> ). 当存在夏令时时区缩写时, 假定使用与 IANA 时区数据库中相同的夏令时转换规则. 在标准 Greenplum 安装时, 与 US/Eastern 相同, 因此 POSIX-风格时区规范遵守 USA 夏令时规则. 如果需要，你也可以通过替换文件来调整这个行为.

总之, 这就是时区全称与缩写的不同: 缩写表示与 UTC 之间的偏移, 而全称还暗示了夏令时规则, 因此有两种可能的 UTC 偏移. 例如, 2014-06-04 12:00 America/New_York 表示纽约本地时间中午, 指定为东部夏令时间 Eastern Daylight Time (UTC-4). 因此 2014-06-04 12:00 EDT 指定了同样的时间常数. 但是 2014-06-04 12:00 EST 指定为东部标准时间 (UTC-5) 中午, 不管那天夏令时是否正常生效与否.

另外复杂情况是, 有些管辖范围使用同样的时区缩写表示不同的 UTC 偏移和不同的时间; 例如, 在 MSK 有些年头表示 UTC+3, 有些年头又表示 UTC+4. 对于这些缩写, Greenplum 根据日期来进行解释(对于未来时间，根据最近的情况解释); 但是, 就像上面 EST 这个例子, 这不一定与那个日期的本地时间相同.

应该小心 POSIX-风格时区特征很容易导致错误输入被默默接受, 因为很难检查时区输入的合理性. 例如, SET TIMEZONE TO FOOBAR0 能够正常工作, 让系统有效使用相当特别的 UTC 缩写. 另一个要记住的问题是在 POSIX 时区名称中, 正数被用在(相对于)格林威治时间. 其他任何地方, Greenplum 遵守 ISO-8601 约定, 正时区偏移都是(相对于)格林威治时间.

所有情况中, 失去名称和缩小都是大小写无关的.

不论时区名称还是缩写, 都没有硬编码进服务程序; 我们是从配置文件 .

TimeZone 配置参数可以在文件中设定, 或者其他任何标准设置配置参数的方式. 另外也有一个特殊方式来设置:

1. SQL 命令 SET TIME ZONE 可以设置当前会话的时区. 这是比 SET TIMEZONE TO 更符合 SQL 兼容规范的一种形式.
2. PGTZ 环境变量被 libpq 客户端用来, 在初始化与服务器的连接时, 发送 SET TIME ZONE 命令.

时间段输入

时间段 值可以被写成下面的详细语法:

@ quantity unit quantity unit... direction

这里 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 节内容 format with designators, 或者用 4.4.3.3 节说明的 alternative format. format with designators 看起来像这个样子:

P quantity unit quantity unit...  T  quantity unit...

这个时间段字符串必须以 P 开头, 可以包含一个 T 来隔开时间部分. 可以使用的缩写列在 Table 8 中. 单位可以忽略, 也可以按任意顺序指定, 但是时间单位必须出现在 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 秒. 另外, 带有 to the right 字段值中 fields 允许的右边最不重要的字段会被简单的忽略. 例如, 写 INTERVAL ‘1 day 2:03:04’ HOUR TO MINUTE 结果或丢弃秒部分, 而不会丢掉天的部分.

根据 SQL 标准, 时间段中所有部分必须有同样的符号, 开头的负号会对所有部分起作用; 例如, 在时间段字面值 ‘-1 2:03:04’ 中, 负号应用于天, 以及小时/分/秒 部分. Greenplum 允许各部分有不同的符号, 并且传统认为文本中每个部分有独立的符号, 因此上面例子中小时/分/秒会被认为是正值. 如果 IntervalStyle 设置给 sql_standard 那么开头的负号就应用到所有部分 (只有当别的地方没有符号时). 要不然, 就用传统 Greenplum 解释. 为了避免模糊, 如果时间段值是负的, 建议每个部分都带上负号.

在详细日期格式中, 对于更紧凑的输入格式的几个部分值可以带小数; 例如 ‘1.5 week’ 或者 ‘01:02:03.45’. 这些输入被转换为月, 天和秒来存储. 当这些导致结果的月或者天有小数时, 小数部分被加到低一级的部分上. 转换规则是 1 月 = 30 天, 1 天 = 24 小时. 例如, ‘1.5 month’ 变成 1 月 15 天. 输出中秒从来不会显示小数部分.

Table 9 这里给出 时间段 输入的有效示例.

内部地 时间段 值被存为月, 天, 和秒. 这是因为一个月中的天数是变化的, 另外因为夏令时的原因, 一天可能有23, 24 或 25 小时. 月和天部分是整数, 秒可以包含小数. 因为时间段通常从常量字符串或 时间戳 相减得来, 大部分情况下这种存储挺好, 但有时也有一些意外: SELECT EXTRACT(hours from ‘80 minutes’::interval); date_part —————- 1 SELECT EXTRACT(days from ‘80 hours’::interval); date_part —————- 0 当超过值的正常范围时, 可以用 justify_days 函数和 justify_hours 函数用来调整天和小时.

时间段输出

时间段类型的输出格式可以设置为以下四种格式之一: sql_standard, postgres, postgres_verbose, 或者 iso_8601, 通过命令 SET intervalstyle 来设置. 默认是 postgres 格式. Table 10 里有每种输出格式的一些例子.

如果时间段值符合标准要求 (年-月 或者 天-时间, 没有混合正负数), 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 的描述一致.

Parent topic: Greenplum 数据库数据类型