8.5.日期/时间类型

8.5.1. 日期/时间输入

8.5.2. 日期/时间输出

8.5.3. 时区

8.5.4. 区间输入

8.5.5. 间隔输出

PostgreSQL支持完整的SQL日期和时间类型，如中所示表8.9。中介绍了这些数据类型的可用操作第9.9节。日期是根据公历计算的，即使是在公历引入之前的几年（参见B.6节更多信息）。

表8.9.日期/时间类型

名称	存储大小	描述	低值	高价值	决议
时间戳[(*p*)[无时区]	8字节	日期和时间（无时区）	公元前4713年	公元294276年	1微秒
时间戳[(*p*)]时区	8字节	日期和时间，带时区	公元前4713年	公元294276年	1微秒
日期	4字节	日期（一天中没有时间）	公元前4713年	公元5874897年	1天
时间[(*p*)[无时区]	8字节	一天中的时间（无日期）	00:00:00	24:00:00	1微秒
时间[(*p*)]时区	12字节	一天中的时间（无日期），带时区	00:00:00+1559	24:00:00-1559	1微秒
间隔[*领域* ] [ (*p*) ]	16字节	时间间隔	-1.78亿年	1.78亿年	1微秒

笔记

SQL标准要求只编写时间戳相当于不带时区的时间戳，PostgreSQL尊重这种行为。时间戳被接受为的缩写带时区的时间戳; 这是一个PostgreSQL扩展。

时间, 时间戳和间隔接受可选的精度值*p它指定秒字段中保留的小数位数。默认情况下，精度没有明确的界限。允许的范围p*从0到6.

这个间隔type还有一个附加选项，即通过写入以下短语之一来限制存储字段集：

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

请注意，如果两者都*领域和p如果指定了领域*必须包括第二，因为精度仅适用于秒。

类型带时区的时间是由SQL标准定义的，但该定义显示的属性会导致有用性受到质疑。在大多数情况下日期, 时间, 不带时区的时间戳和带时区的时间戳应提供所有应用程序所需的完整日期/时间功能。

8.5.1.日期/时间输入

日期和时间输入几乎可以采用任何合理的格式，包括ISO 8601、SQL兼容、传统POSTGRES等。对于某些格式，日期输入中的日、月和年的顺序不明确，并且支持指定这些字段的预期顺序。设定日期风格参数到麦迪要选择月-日-年解释，DMY选择日-月-年解释，或YMD选择年-月-日解释。

PostgreSQL在处理日期/时间输入方面比SQL标准要求的更灵活。看见附录B对于日期/时间输入的准确解析规则以及识别文本字段（包括月份、周天数和时区）。

记住，任何日期或时间文字输入都需要用单引号括起来，比如文本字符串。提到第4.1.2.7节了解更多信息。SQL需要以下语法

type [ (p) ] 'value'

哪里*p*是一种可选精度规格，给出秒字段中的小数位数。精度可以指定为时间, 时间戳和间隔类型，范围从0到6.如果常量规范中未指定精度，则默认为文字值的精度（但不超过6位）。

8.5.1.1.日期

表8.10显示了一些可能的输入日期类型

表8.10.日期输入

实例	描述
1999-01-08	ISO 8601；1月8日任何模式（推荐格式）
1999年1月8日	毫不含糊日期风格输入模式
1/8/1999	1月8日麦迪模式8月1日DMY模式
1/18/1999	1月18日麦迪模式在其他模式下被拒绝
01/02/03	2003年1月2日麦迪模式2003年2月1日DMY模式2001年2月3日YMD模式
1999-1-08	1月8日在任何模式下
1999年1月8日	1月8日在任何模式下
08-Jan-1999	1月8日在任何模式下
99-01-08	1月8日YMD模式，否则错误
1999年1月8日	1月8日，除了错误YMD模式
1999年1月8日	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.时代

一天中的时间类型是时间[(*p*)]没有时区和时间[(*p*)]时区. 时间独自一人相当于没有时区的时间.

这些类型的有效输入包括一天中的某个时间，后跟一个可选时区。（见表8.11和表8.12。如果在输入中指定了时区没有时区的时间，它被默默地忽略了。您也可以指定日期，但它将被忽略，除非您使用的时区名称涉及夏令时规则，例如美国/纽约.在这种情况下，需要指定日期，以确定是否适用标准时间或夏令时。相应的时区偏移记录在带时区的时间价值

表8.11.时间输入

实例	描述
04:05:06.789	ISO 8601
04:05:06	ISO 8601
04:05	ISO 8601
040506	ISO 8601
凌晨4:05	同04:05；AM不影响价值
下午4:05	与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太平洋标准时间	由缩写指定的时区
2003-04-12 04:05:06美国/纽约	全名指定的时区

表8.12.时区输入

实例	描述
PST	缩写（代表太平洋标准时间）
美国/纽约	完整时区名称
PST8PDT	POSIX风格的时区规范
-8:00:00	太平洋标准时间的UTC偏移量
-8:00	用于PST的UTC偏移量（ISO 8601扩展格式）
-800	用于PST的UTC偏移量（ISO 8601基本格式）
-8	用于PST的UTC偏移量（ISO 8601基本格式）
祖鲁人	UTC的军事缩写
z	缩写祖鲁人（也在ISO 8601中）

提到第8.5.3节有关如何指定时区的详细信息。

8.5.1.3.时间戳

时间戳类型的有效输入包括日期和时间的串联，后跟可选时区，后跟可选时间公元或公元前（或者，公元/公元前可以出现在时区之前，但这不是首选顺序。）因此：

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 '2004-10-19 10:23:54'

是一个不带时区的时间戳虽然

TIMESTAMP '2004-10-19 10:23:54+02'

是一个带时区的时间戳.PostgreSQL在确定文本字符串的类型之前从不检查其内容，因此会将上述两种类型都视为不带时区的时间戳.以确保文字被视为带时区的时间戳，为其指定正确的显式类型：

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

在一个已经被确定为不带时区的时间戳，PostgreSQL将自动忽略任何时区指示。也就是说，结果值是从输入值中的日期/时间字段导出的，并且不针对时区进行调整。

对于带时区的时间戳，内部存储的值始终以UTC（世界协调时间，传统上称为格林威治标准时间，GMT）为单位。指定了明确时区的输入值将使用该时区的适当偏移量转换为UTC。如果输入字符串中没有指定时区，则假定它位于系统的时区参数，并使用时区区

当带时区的时间戳值是输出值，它总是从UTC转换为当前值时区区域，并显示为该区域中的本地时间。要查看另一时区中的时间，请更改时区或者使用在时区构造（参见第9.9.4节).

之间的转换不带时区的时间戳和带时区的时间戳通常假设不带时区的时间戳价值应视为时区地方时可以使用为转换指定不同的时区在时区.

8.5.1.4.特殊价值观

为了方便起见，PostgreSQL支持几个特殊的日期/时间输入值，如中所示表8.13.价值观无穷和-无穷大在系统内部有专门的表示，并将不变地显示；但另一些只是符号化的缩写，在读取时会转换为普通的日期/时间值。（尤其是，现在相关字符串在读取后立即转换为特定的时间值。）当在SQL命令中用作常量时，所有这些值都需要用单引号括起来。

表8.13.特殊日期/时间输入

输入字符串	有效类型	描述
纪元	日期, 时间戳	1970-01-01 00:00:00+00（Unix系统时间零点）
无穷	日期, 时间戳	比所有其他时间戳都晚
-无穷大	日期, 时间戳	比所有其他时间戳都早
现在	日期, 时间, 时间戳	当前事务的开始时间
今天	日期, 时间戳	午夜时分(00:00)今天
明天	日期, 时间戳	午夜时分(00:00)明天
昨天	日期, 时间戳	午夜时分(00:00)昨天
所有球	时间	00:00:00.00 UTC

以下与SQL兼容的函数也可用于获取相应数据类型的当前时间值：当前日期, 当前时间, 当前时间戳, 本地时间, 本地时间戳（见第9.9.5节）注意，这些是SQL函数，并且不在数据输入字符串中识别。

小心

当输入字符串现在, 今天, 明天和昨天可以在交互式SQL命令中使用，但当命令保存以供以后执行时，它们可能会有令人惊讶的行为，例如在准备好的语句、视图和函数定义中。可以将字符串转换为特定的时间值，该值在过期很久后仍会继续使用。在这种情况下，请使用其中一个SQL函数。例如当前_日期+1比…更安全“明天”：日期.

8.5.2.日期/时间输出

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

表8.14.日期/时间输出样式

样式说明	描述	实例
国际标准化组织	ISO 8601，SQL标准	1997-12-17 07:37:16-08
SQL	传统风格	1997年12月17日07:37:16.00太平洋标准时间
博士后	原创风格	星期三12月17日07:37:16 1997太平洋标准时间
德国的	地域风格	17.12.1997 07:37:16.00太平洋标准时间

笔记

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

在SQL和POSTGRES样式中，如果指定了DMY字段顺序，则日期显示在月份之前，否则月份显示在日期之前。（见第8.5.1节了解此设置如何影响输入值的解释。）表8.15展示了一些例子。

表8.15.日期顺序约定

日期风格背景	输入排序	示例输出
SQL，DMY	白天/月/年	1997年12月17日15:37:16.00 CET
SQL，MDY	月/白天/年	1997年12月17日07:37:16.00太平洋标准时间
博士后	白天/月/年	1997年12月17日星期三07:37:16太平洋标准时间

在ISO样式中，时区始终显示为与UTC的有符号数字偏移量，正号用于格林威治以东的区域。偏移量将显示为*啊（仅限小时）如果是整小时数，则为啊:嗯如果它是整数分钟数，则为啊:嗯:党卫军（第三种情况在任何现代时区标准中都不可能出现，但在使用标准化时区之前的时间戳时可能会出现。）在其他日期样式中，如果时区在当前区域中常用，则时区显示为字母缩写。否则，它将以ISO 8601基本格式显示为有符号数字偏移量(啊或嗯*).

日期/时间样式可由用户使用设置日期样式指挥部日期风格中的参数postgresql。形态配置文件，或日期样式服务器或客户端上的环境变量。

格式化功能到_char（见第9.8节)也可以通过更灵活的方式设置日期/时间输出的格式。

8.5.3.时区

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

PostgreSQL致力于与SQL标准定义兼容，以用于典型用途。然而，SQL标准的日期和时间类型以及功能混合得很奇怪。两个明显的问题是：

• 虽然日期类型不能有关联的时区时间打字可以。在现实世界中，时区没有什么意义，除非与日期和时间相关联，因为偏移量可以随着夏令时边界的变化而变化。

• 默认时区指定为与UTC的恒定数值偏移。因此，在跨DST边界进行日期/时间算术时，不可能适应夏令时。

  为了解决这些困难，我们建议在使用时区时使用同时包含日期和时间的日期/时间类型。是的不建议使用该类型带时区的时间（尽管PostgreSQL支持传统应用程序和SQL标准）。PostgreSQL假定任何类型的本地时区只包含日期或时间。

  所有时区感知的日期和时间都存储在UTC内部。它们将转换为指定区域内的本地时间时区配置参数，然后再显示给客户端。

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

• 例如，完整的时区名称美国/纽约。已识别的时区名称列在pg_时区_名称查看（参见第52.94节).PostgreSQL为此目的使用了广泛使用的IANA时区数据，因此其他软件也可以识别相同的时区名称。

• 例如，时区缩写PST。这样的规范仅定义了UTC的特定偏移量，与完整时区名称不同，完整时区名称也可能意味着一套夏令时转换规则。已识别的缩略语列在pg_时区_缩写查看（参见第52.93节)。无法设置配置参数时区或日志_时区但您可以在日期/时间输入值和在时区操作人员

• 除了时区名称和缩写外，PostgreSQL还将接受POSIX风格的时区规范，如中所述B.5节。此选项通常不比使用指定时区更可取，但如果没有合适的IANA时区条目可用，则可能需要此选项。

  简而言之，这就是缩写和全名之间的区别：缩写代表UTC的特定偏移量，而许多全名意味着本地夏令时规则，因此有两种可能的UTC偏移量。例如，2014-06-04 12:00美国/纽约表示纽约当地时间中午，该特定日期为东部夏时制（UTC-4）。所以2014-06-04美国东部时间12:00指定同一时刻。但是2014-06-04美国东部时间12:00指定东部标准时间（UTC-5）正午，无论夏时制是否在该日期名义上生效。

  使事情复杂化的是，一些司法管辖区使用同一时区缩写来表示不同时间的不同UTC偏移量；比如在莫斯科MSK在某些年份意味着UTC+3，在其他年份意味着UTC+4.PostgreSQL根据这些缩写在指定日期的意思（或最近的意思）来解释它们；但是，就像美国东部时间例如，这不一定与当天的当地民事时间相同。

  在所有情况下，时区名称和缩写都不区分大小写。（这与8.2之前的PostgreSQL版本有所不同，后者在某些上下文中区分大小写，但在其他上下文中不区分大小写。）

  时区名称和缩写都不会硬连接到服务器中；它们是从存储在下的配置文件中获取的.../共享/时区/和.../共享/时区集/安装目录（请参阅B.4节).

  这个时区可以在文件中设置配置参数postgresql。形态，或中所述的任何其他标准方式第20章。还有一些特殊的设置方法：

• SQL命令设定时区设置会话的时区。这是另一种拼写将时区设置为使用更符合SQL规范的语法。

• 这个PGTZlibpq客户端使用环境变量发送设定时区命令在连接时发送到服务器。

8.5.4.区间输入

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

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

哪里*量是一个数字（可能有签名）；单元是微秒, 毫秒, 第二, 分钟, 小时, 白天, 周, 月, 年, 十年, 100年, 一千年，或这些单位的缩写或复数；方向*可以是以前或者是空的。at标志(@)是可选的噪音。不同单位的金额以适当的符号隐式添加。以前否定所有字段。如果需要，此语法也用于间隔输出间隔方式即将博士后冗长.

可以指定天、小时、分钟和秒的数量，而无需明确的单位标记。例如'1 12:59:10'读起来和“1天12小时59分10秒”。此外，年和月的组合可以用破折号指定；例如'200-10'读起来和“200年10个月”（事实上，SQL标准只允许使用这些较短的表单，并且在间隔方式即将sql_标准.)

间隔值也可以使用标准第4.4.3.2节的“带指示符的格式”或第4.4.3.3节的“替代格式”写入ISO 8601时间间隔。带有指示符的格式如下所示：

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

当用*领域或将字符串指定给使用领域规范中，未标记数量的解释取决于领域例如间隔“1”年读作1年，而间隔“1”意思是1秒。此外，字段值位于领域*规范被悄悄地丢弃。例如，写作间隔“1天2:03:04”小时到分钟结果会删除秒字段，但不会删除日字段。

根据SQL标准，区间值的所有字段必须具有相同的符号，因此前导负号适用于所有字段；例如，区间文字中的负号'-1 2:03:04'适用于天和小时/分钟/秒部分。PostgreSQL允许字段具有不同的符号，并且传统上将文本表示中的每个字段视为独立的符号，因此在本例中，小时/分钟/秒部分被认为是正的。如果间隔方式即将sql_标准然后，前导符号被认为适用于所有字段（但只有在没有额外符号出现的情况下）。否则，将使用传统的PostgreSQL解释。为了避免歧义，如果任何字段为负数，建议在每个字段上加一个明确的符号。

字段值可以有小数部分：例如，“1.5周”或'01:02:03.45'但是，由于interval内部只存储三个整数单位（月、日、微秒），所以分数单位必须溢出到更小的单位。大于月的单位的小数部分被截断为整数个月，例如：。“1.5年”变成“1年6个月”.周和天的分数部分计算为天数和微秒的整数，假设每月30天，每天24小时，例如：。，“1.75个月”变成1周一22天12:00:00.只有秒会在输出时显示为分数。

表8.17显示了一些有效的示例间隔输入

表8.17.区间输入

实例	描述
1-2	SQL标准格式：1年2个月
3 4:05:06	SQL标准格式：3天4小时5分6秒
1年2个月3天4小时5分6秒	传统的博士后形式：1年2个月3天4小时5分6秒
P1Y2M3DT4H5M6S	ISO 8601“带指示符的格式”：与上述含义相同
P0001-02-03T04:05:06	ISO 8601“替代格式”：与上述含义相同

内部间隔值存储为月、日和微秒。这样做是因为一个月的天数不同，如果涉及夏令时调整，一天可以有23或25个小时。月份和天数字段是整数，而微秒字段可以存储小数秒。因为间隔通常是由常量字符串或时间戳减法，这种存储方法在大多数情况下运行良好，但可能会导致意外的结果：

SELECT EXTRACT(hours from '80 minutes'::interval);
 date_part
### 8.5.5. Interval Output

[]()

 The output format of the interval type can be set to one of the four styles `sql_standard`, `postgres`, `postgres_verbose`, or `iso_8601`, using the command `SET intervalstyle`. The default is the `postgres` format. [Table 8.18](datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE) shows examples of each output style.

 The `sql_standard` style produces output that conforms to the SQL standard's specification for interval literal strings, if the interval value meets the standard's restrictions (either year-month only or day-time only, with no mixing of positive and negative components). Otherwise the output looks like a standard year-month literal string followed by a day-time literal string, with explicit signs added to disambiguate mixed-sign intervals.

 The output of the `postgres` style matches the output of PostgreSQL releases prior to 8.4 when the [DateStyle](runtime-config-client.html#GUC-DATESTYLE) parameter was set to `ISO`.

 The output of the `postgres_verbose` style matches the output of PostgreSQL releases prior to 8.4 when the `DateStyle` parameter was set to non-`ISO` output.

 The output of the `iso_8601` style matches the “format with designators” described in section 4.4.3.2 of the ISO 8601 standard.

**Table 8.18. Interval Output Style Examples**

|Style Specification|Year-Month Interval|      Day-Time Interval       |                 Mixed Interval                  |
|-------------------|-------------------|------------------------------|-------------------------------------------------|
|  `sql_standard`   |        1-2        |          3 4:05:06           |                \-1-2 +3 -4:05:06                |
|    `postgres`     |   1 year 2 mons   |       3 days 04:05:06        |       \-1 year -2 mons +3 days -04:05:06        |
|`postgres_verbose` |  @ 1 year 2 mons  |@ 3 days 4 hours 5 mins 6 secs|@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago|
|    `iso_8601`     |       P1Y2M       |          P3DT4H5M6S          |              P-1Y-2M3D​T-4H-5M-6S               |