登录8.5. 日期/时间类型#
PostgreSQL支持SQL日期和时间类型的全套类型，如表 8.9所示。这些数据类型可用的操作在第 9.9 节中进行了描述。日期按照格里高利历进行计算，即使是在该历法引入之前的年份（有关详细信息，请参阅第 B.6 节）。
表 8.9. 日期/时间类型
| 名称 | 存储大小 | 说明 | 低值 | 高值 | 分辨率 |
|---|---|---|---|---|---|
timestamp [ ( | 8 字节 | 日期和时间（无时区） | 公元前 4713 年 | 公元 294276 年 | 1 微秒 |
timestamp [ ( | 8 字节 | 日期和时间，带时区 | 公元前 4713 年 | 公元 294276 年 | 1 微秒 |
date | 4 字节 | 日期（无时间） | 公元前 4713 年 | 公元 5874897 年 | 1 天 |
时间 [ ( | 8 字节 | 时间（无日期） | 00:00:00 | 24:00:00 | 1 微秒 |
时间 [ ( | 12 字节 | 时间（无日期），含时区 | 00:00:00+1559 | 24:00:00-1559 | 1 微秒 |
间隔 [ | 16 字节 | 时间间隔 | -178000000 年 | 178000000 年 | 1 微秒 |
注意
SQL 标准要求仅编写timestamp等效于timestamp without time zone，PostgreSQL遵循此行为。timestamptz被接受为timestamp with time zone的缩写；这是PostgreSQL扩展。
time、timestamp和interval接受可选精度值*p，该值指定秒字段中保留的小数位数。默认情况下，精度没有明确的界限。p*的允许范围为 0 到 6。
interval类型有一个附加选项，即通过编写以下短语之一来限制存储字段的集合
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的组合应该提供任何应用程序所需的完整日期/时间功能。
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类型指定，并且可以从 0 到 6。如果在常量规范中未指定精度，则默认为文字值的精度（但不得超过 6 位数字）。
8.5.1.1. 日期#
表 8.10显示了date类型的某些可能输入。
表 8.10. 日期输入
| 示例 | 说明 |
|---|---|
| 1999-01-08 | ISO 8601；任何模式下的 1 月 8 日（推荐格式） |
| 1999 年 1 月 8 日 | 在任何 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-01-08 | 在任何模式下均为 1 月 8 日 |
| 01-08-1999 | 在任何模式下均为 1 月 8 日 |
| 08-01-1999 | 在任何模式下均为 1 月 8 日 |
| 99-01-08 | 在 YMD 模式下为 1 月 8 日，否则报错 |
| 08-01-99 | 1 月 8 日，但 YMD 模式下会报错 |
| 01-08-99 | 1 月 8 日，但 YMD 模式下会报错 |
| 19990108 | ISO 8601；在任何模式下均为 1999 年 1 月 8 日 |
| 990108 | ISO 8601；在任何模式下均为 1999 年 1 月 8 日 |
| 1999.008 | 年份和一年中的天数 |
| J2451187 | 儒略日 |
| 公元前 99 年 1 月 8 日 | 公元前 99 年 |
8.5.1.2. 时间#
时间类型为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，时区为 UTC 偏移 |
04:05:06-08:00 | ISO 8601，时区为 UTC 偏移 |
04:05-08:00 | ISO 8601，时区为 UTC 偏移 |
040506-08 | ISO 8601，时区为 UTC 偏移 |
040506+0730 | ISO 8601，时区为 UTC 偏移的小时数 |
040506+07:30:00 | UTC 偏移指定为秒（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:00 | PST 的 UTC 偏移量 |
-8:00 | PST 的 UTC 偏移量（ISO 8601 扩展格式） |
-800 | PST 的 UTC 偏移量（ISO 8601 基本格式） |
-8 | PST 的 UTC 偏移量（ISO 8601 基本格式） |
zulu | UTC 的军事缩写 |
z | zulu 的简写（也用于 ISO 8601 中） |
请参阅第 8.5.3 节以获取有关如何指定时区的更多信息。
8.5.1.3. 时间戳#
时间戳类型的有效输入由日期和时间连接而成，后跟一个可选时区，再后跟一个可选的AD或BC。（或者，AD/BC可以出现在时区之前，但这并不是首选顺序。）因此
1999-01-08 04:05:06和
1999-01-08 04:05:06 -8:00是有效值，它们遵循ISO8601 标准。此外，还支持常见格式
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.4 节）。
timestamp without time zone和timestamp with time zone之间的转换通常假设timestamp without time zone值应采用或给出为timezone本地时间。可以使用AT TIME ZONE为转换指定不同的时区。
8.5.1.4. 特殊值#
PostgreSQL支持多种特殊日期/时间输入值以方便使用，如表 8.13所示。值infinity和-infinity在系统内部以特殊方式表示，并且将保持不变地显示；但其他值只是简单的符号缩写，在读取时将转换为普通日期/时间值。（特别是，now和相关字符串在读取时将转换为特定时间值。）所有这些值在作为 SQL 命令中的常量使用时需要用单引号引起来。
表 8.13. 特殊日期/时间输入
| 输入字符串 | 有效类型 | 说明 |
|---|---|---|
epoch | date、timestamp | 1970-01-01 00:00:00+00（Unix 系统时间零） |
infinity | date、timestamp | 晚于所有其他时间戳 |
-infinity | date、timestamp | 早于所有其他时间戳 |
now | date、time、timestamp | 当前事务的开始时间 |
today | date、timestamp | 今天的午夜（00:00） |
tomorrow | date、timestamp | 明天的午夜（00:00） |
yesterday | date、timestamp | 昨天的午夜（00:00） |
allballs | time | 00:00:00.00 UTC |
以下SQL兼容函数也可用于获取相应数据类型的当前时间值：CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP。（请参阅第 9.9.5 节。）请注意，这些是 SQL 函数，在数据输入字符串中不识别。
警告
虽然输入字符串now、today、tomorrow和yesterday在交互式 SQL 命令中使用时很好，但在将命令保存为稍后执行时，例如在预处理语句、视图和函数定义中，它们可能会产生令人惊讶的行为。该字符串可以转换为特定时间值，并且在它变得陈旧后很长时间内继续使用。在这种情况下，请改用其中一个 SQL 函数。例如，CURRENT_DATE + 1比'tomorrow'::date更安全。
8.5.2. 日期/时间输出#
日期/时间类型的输出格式可以设置为四种样式之一：ISO 8601、SQL（Ingres）、传统POSTGRES（Unixdate格式）或德语。默认格式为ISO格式。（SQL标准要求使用 ISO 8601 格式。““SQL”” 输出格式的名称是一个历史意外。）表 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 |
德语 | 区域样式 | 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 | day/month/year | 17/12/1997 15:37:16.00 CET |
SQL，MDY | month/day/year | 12/17/1997 07:37:16.00 PST |
Postgres，DMY | day/month/year | Wed 17 Dec 07:37:16 1997 PST |
在ISO样式中，时区始终显示为与 UTC 的带符号数字偏移量，对于格林尼治以东的时区使用正号。如果偏移量是整数小时数，则显示为*hh（仅小时）；否则，如果偏移量是整数分钟数，则显示为hh:mm；否则，显示为hh:mm:ss。（第三种情况在任何现代时区标准中都不可能出现，但它可能出现在处理早于采用标准化时区的的时间戳时。）在其他日期样式中，如果时区在当前时区中常用，则时区显示为字母缩写。否则，它以 ISO 8601 基本格式（hh或hhmm*）显示为带符号数字偏移量。
用户可以使用SET datestyle命令、postgresql.conf配置文件中的DateStyle参数或服务器或客户端上的PGDATESTYLE环境变量来选择日期/时间样式。
格式化函数to_char（参见第 9.8 节）也可作为一种更灵活的方式来格式化日期/时间输出。
8.5.3. 时区#
时区和时区惯例受政治决策影响，而不仅仅是地球几何形状。世界各地的时区在 20 世纪得到一定程度的标准化，但仍然容易发生任意更改，尤其是在夏令时规则方面。PostgreSQL使用广泛使用的 IANA（Olson）时区数据库来获取有关历史时区规则的信息。对于未来的时间，假设给定时区的最新已知规则将在无限远的未来继续被遵守。
PostgreSQL努力与SQL标准定义兼容，以供典型使用。但是，SQL标准对日期和时间类型和功能有着奇怪的混合。两个明显的问题是
虽然
date类型不能有相关的时区，但time类型可以。现实世界中的时区如果没有与日期和时间相关联，几乎没有意义，因为偏移量可能会随着夏令时边界而全年变化。默认时区指定为从 的恒定数字偏移量。因此，在跨 边界进行日期/时间算术时，不可能适应夏令时。
为了解决这些困难，我们建议在使用时区时使用同时包含日期和时间的日期/时间类型。我们不建议使用类型time with time zone（尽管PostgreSQL为旧版应用程序和符合SQL标准而支持它）。PostgreSQL假设您仅包含日期或时间的任何类型的本地时区。
所有与时区相关联的日期和时间都以UTC内部存储。在显示给客户端之前，它们会根据TimeZone配置参数指定的区域转换为本地时间。
PostgreSQL允许您以三种不同的形式指定时区
一个完整的时区名称，例如
America/New_York。已识别的时区名称列在pg_timezone_names视图中（参见 第 54.32 节）。PostgreSQL 为此使用广泛使用的 IANA 时区数据，因此其他软件也识别相同的时区名称。时区缩写，例如
PST。这种规范仅定义了与 UTC 的特定偏移量，而完整的时区名称还可以暗示一组夏令时转换规则。已识别的缩写列在pg_timezone_abbrevs视图中（参见 第 54.31 节）。您无法将配置参数 TimeZone 或 log_timezone 设置为时区缩写，但您可以在日期/时间输入值中和使用AT TIME ZONE运算符时使用缩写。除了时区名称和缩写之外，PostgreSQL 还将接受 POSIX 样式时区规范，如 第 B.5 节 中所述。此选项通常不如使用命名时区好，但如果没有任何合适的 IANA 时区条目，则可能需要此选项。
简而言之，缩写和全名之间的区别在于：缩写表示与 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/下存储的配置文件中获取的（请参见第 B.4 节）。
可以在文件postgresql.conf中设置TimeZone配置参数，也可以在第 20 章中描述的任何其他标准方式中设置。还有一些特殊的方法可以设置它
命令
SET TIME ZONE为会话设置时区。这是SET TIMEZONE TO的替代拼写，具有更兼容 SQL 规范的语法。环境变量
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或空。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 quantity unit [ quantity unit ...] [ T [ quantity unit ...]]字符串必须以P开头，并且可以包含T，它会引入时间单位。可在表 8.16中找到可用的单位缩写。可以省略单位，并且可以按任何顺序指定单位，但小于一天的单位必须出现在T之后。特别是，M的含义取决于它是在T之前还是之后。
表 8.16. ISO 8601 间隔单位缩写
| 缩写 | 含义 |
|---|---|
| Y | 年 |
| M | 月份（在日期部分） |
| W | 周 |
| D | 天 |
| H | 小时 |
| M | 分钟（在时间部分） |
| S | 秒 |
在备用格式中
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解释。为避免歧义，建议为每个字段附加显式符号（如果任何字段为负数）。
字段值可以有小数部分：例如，'1.5 weeks'或'01:02:03.45'。但是，由于间隔在内部仅存储三个整数单位（月、天、微秒），因此必须将分数单位溢出到较小的单位。大于月份的单位的分数部分将被四舍五入为月份的整数，例如'1.5 years'将变为'1 year 6 mons'。周和天的分数部分将被计算为天的整数和微秒，假设每月 30 天，每天 24 小时，例如，'1.75 months'将变为1 mon 22 days 12:00:00。只有秒会在输出中显示为分数。
表 8.17显示了一些有效的interval输入示例。
表 8.17。间隔输入
| 示例 | 说明 |
|---|---|
1-2 | SQL 标准格式：1 年 2 个月 |
3 4:05:06 | SQL 标准格式：3 天 4 小时 5 分钟 6 秒 |
1 年 2 个月 3 天 4 小时 5 分钟 6 秒 | 传统 Postgres 格式：1 年 2 个月 3 天 4 小时 5 分钟 6 秒 |
P1Y2M3DT4H5M6S | ISO 8601 “带设计器的格式”：与上述含义相同 |
P0001-02-03T04:05:06 | ISO 8601 “备用格式”：与上述含义相同 |
在内部，interval值存储为月、日和微秒。这样做是因为一个月中的天数是可变的，如果涉及夏令时调整，一天可能有 23 或 25 小时。月和日字段为整数，而微秒字段可以存储小数秒。由于间隔通常从常量字符串或timestamp减法创建，因此这种存储方法在大多数情况下都能正常工作，但可能会导致意外结果
SELECT EXTRACT(hours from '80 minutes'::interval);
date_part
-----------
1
SELECT EXTRACT(days from '80 hours'::interval);
date_part
-----------
0函数justify_days和justify_hours可用于调整超出其正常范围的天数和小时数。
8.5.5. 间隔输出#
间隔类型的输出格式可以设置为四种样式之一sql_standard、postgres、postgres_verbose或iso_8601，使用命令SET intervalstyle。默认值为postgres格式。表 8.18显示了每种输出样式的示例。
如果间隔值符合标准的限制（仅年-月或仅日-时，没有正负分量的混合），sql_standard样式会生成符合 SQL 标准间隔文字字符串规范的输出。否则，输出看起来像一个标准的年-月文字字符串，后跟一个日-时文字字符串，并添加显式符号以消除符号混合间隔的歧义。
当DateStyle参数设置为ISO时，postgres样式的输出与 8.4 之前的PostgreSQL版本的输出相匹配。
当DateStyle参数设置为非ISO输出时，postgres_verbose样式的输出与 8.4 之前的PostgreSQL版本的输出相匹配。
iso_8601样式的输出与 ISO 8601 标准第 4.4.3.2 节中描述的““带设计器的格式””相匹配。
表 8.18. 间隔输出样式示例
| 样式规范 | 年-月间隔 | 日-时间间隔 | 混合间隔 |
|---|---|---|---|
sql_standard | 1-2 | 3 4:05:06 | -1-2 +3 -4:05:06 |
postgres | 1 年 2 个月 | 3 天 04:05:06 | -1 年 -2 个月 +3 天 -04:05:06 |
postgres_verbose | @ 1 年 2 个月 | @ 3 天 4 小时 5 分钟 6 秒 | @ 1 年 2 个月 -3 天 4 小时 5 分钟 6 秒前 |
iso_8601 | P1Y2M | P3DT4H5M6S | P-1Y-2M3DT-4H-5M-6S |