8.5. 日期/时间类型

PostgreSQL 支持完整的 SQL 日期和时间类型，如Table 8.9所示。这些数据类型上可用的操作在Section 9.9中描述。日期是根据公历计算的，即使是在采用该 calendar 之前的年份也是如此(有关更多信息，请参见Section B.5)。

表 8.9. 日期/时间类型

Name	Storage Size	Description	Low Value	High Value	Resolution
`timestamp [ (p) ] [ without time zone ]`	8 bytes	日期和时间(无时区)	4713 BC	294276 AD	1 microsecond
`timestamp [ (p) ] with time zone`	8 bytes	日期和时间，以及时区	4713 BC	294276 AD	1 microsecond
`date`	4 bytes	日期(无时间)	4713 BC	5874897 AD	1 day
`time [ (p) ] [ without time zone ]`	8 bytes	一天中的时间(无日期)	00:00:00	24:00:00	1 microsecond
`time [ (p) ] with time zone`	12 bytes	一天中的时间(无日期)，带有时区	00:00:00+1459	24:00:00-1459	1 microsecond
`interval [ fields ] [ (p) ]`	16 bytes	time interval	-178000000 years	178000000 years	1 microsecond

Note

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的组合应提供任何应用程序所需的完整日期/时间功能范围。

类型abstime和reltime是内部使用的较低精度类型。不鼓励您在应用程序中使用这些类型。这些内部类型可能会在将来的版本中消失。

8 .5.1. 日期/时间 Importing

日期和时间 Importing 几乎可以采用任何合理的格式，包括 ISO 8601，SQL 兼容，传统的 POSTGRES 等。对于某些格式，Importing 的日期，月份和年份的 Sequences 不明确，并且支持指定这些字段的预期 Sequences。将DateStyle参数设置为MDY以选择月-日-年解释，DMY选择日-月-年解释，或YMD选择年-月-日解释。

PostgreSQL 比 SQL 标准更灵活地处理日期/时间 Importing。有关日期/时间 Importing 的确切解析规则以及可识别的文本字段(包括月份，星期几和时区)，请参见Appendix B。

请记住，任何日期或时间 LiteralsImporting 都需要用单引号括起来，例如文本字符串。有关更多信息，请参考Section 4.1.2.7。 SQL 需要以下语法

type [ (p) ] 'value'

其中* p *是可选的精度规格，在秒字段中给出小数位数。可以为time，timestamp和interval类型指定精度，范围可以从 0 到 6.如果在常量说明中未指定精度，则默认为 Literals 值的精度(但不超过 6 位数字)。

8.5.1.1. Dates

Table 8.10显示了date类型的一些可能的 Importing。

表 8.10. 日期 Importing

Example	Description
1999-01-08	ISO 8601； 1 月 8 日，采用任何模式(推荐格式)
1999 年 1 月 8 日	在任何`datestyle`Importing 模式下均无歧义
1/8/1999	1 月 8 日以`MDY`模式运行； 8 月 1 日进入`DMY`模式
1/18/1999	1 月 18 日以`MDY`模式运行；在其他模式下被拒绝
01/02/03	2003 年 1 月 2 日，以`MDY`模式运行； 2003 年 2 月 1 日，以`DMY`模式运行； 2001 年 2 月 3 日，处于`YMD`模式
1999-Jan-08	1 月 8 日以任何方式
Jan-08-1999	1 月 8 日以任何方式
08-Jan-1999	1 月 8 日以任何方式
99-Jan-08	1 月 8 日以`YMD`模式运行，否则发生错误
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	Julian date
公元前 99 年 1 月 8 日	西元前 99 年

8.5.1.2. Times

时段类型为time [ (p) ] without time zone和time [ (p) ] with time zone。 time等效于time without time zone。

这些类型的有效 Importing 包括一天中的时间，然后是可选的时区。 (请参阅Table 8.11和Table 8.12。)如果在time without time zone的 Importing 中指定了时区，则将忽略该时区。您还可以指定一个日期，但是它将被忽略，除非您使用的是涉及夏时制规则的时区名称，例如America/New_York。在这种情况下，需要指定日期才能确定标准时间还是夏令时。适当的时区偏移量记录在time with time zone值中。

表 8.11. 时间 Importing

Example	Description
`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 相同；Importing 小时必须小于等于 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. 时区 Importing

Example	Description
`PST`	缩写(太平洋标准时间)
`America/New_York`	全时区名称
`PST8PDT`	POSIX 风格的时区规范
`-8:00`	PST 的 ISO-8601 偏移
`-800`	PST 的 ISO-8601 偏移
`-8`	PST 的 ISO-8601 偏移
`zulu`	UTC 的军事缩写
`z`	`zulu`的简写形式

有关如何指定时区的更多信息，请参考Section 8.5.3。

8 .5.1.3. 时标

时间戳记类型的有效 Importing 包括日期和时间的串联，后跟一个可选的时区，再跟一个可选的AD或BC。 (或者，AD/BC可以出现在时区之前，但这不是首选的 Sequences.)因此：

1999-01-08 04:05:06

and:

1999-01-08 04:05:06 -8:00

是有效值，遵循 ISO 8601 标准。另外，常用格式：

January 8 04:05:06 1999 PST

is supported.

SQL 标准通过存在“”或“-”符号以及时间后的时区偏移量来区分timestamp without time zone和timestamp with time zoneLiterals。因此，根据标准，

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

是timestamp without time zone，而

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

是timestamp with time zone。 PostgreSQL 在确定 Literals 字符串的类型之前从未检查过 Literals 字符串的内容，因此会将以上两者都视为timestamp without time zone。为了确保将 Literals 视为timestamp with time zone，请为其提供正确的显式类型：

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

在确定为timestamp without time zone的 Literals 中，PostgreSQL 将默默地忽略任何时区指示。即，结果值是从 Importing 值中的日期/时间字段派生的，并且未针对时区进行调整。

对于timestamp with time zone，内部存储的值始终以 UTC(通用协调时间，通常称为 Greenwich 标准时间，GMT)表示。使用该时区的适当偏移量，将指定了明确时区的 Importing 值转换为 UTC。如果在 Importing 字符串中未指定时区，则假定该时区位于系统的TimeZone参数指示的时区中，并使用timezone时区的偏移量转换为 UTC。

输出timestamp with time zone值时，它将始终从 UTC 转换为当前timezone区域，并在该区域中显示为本地时间。要查看其他时区的时间，请更改timezone或使用AT TIME ZONE构造(请参见Section 9.9.3)。

timestamp without time zone和timestamp with time zone之间的转换通常假定timestamp without time zone值应采用timezone或作为当地时间。可以使用AT TIME ZONE为转换指定不同的时区。

8 .5.1.4. 特殊价值

为了方便起见，PostgreSQL 支持几个特殊的日期/时间 Importing 值，如Table 8.13所示。值infinity和-infinity在系统内部专门表示，将保持不变。但其他仅仅是符号速记，在读取时将转换为普通的日期/时间值。 (特别是now和相关的字符串在读取后立即转换为特定的时间值.)当在 SQL 命令中将所有这些值用作常量时，所有这些值都必须用单引号引起来。

表 8.13. 特殊的日期/时间 Importing

Input String	Valid Types	Description
`epoch`	`date` , `timestamp`	1970 -01-01 00:00:00 00(Unix 系统时间为零)
`infinity`	`date` , `timestamp`	晚于所有其他时间戳
`-infinity`	`date` , `timestamp`	早于所有其他时间戳记
`now`	`date` , `time` , `timestamp`	当前 Transaction 的开始时间
`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。后四个接受可选的亚秒精度规格。 (请参阅Section 9.9.4。)请注意，这些是 SQL 函数，在数据 Importing 字符串中不能识别。

8 .5.2. 日期/时间输出

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

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

Style Specification	Description	Example
`ISO`	ISO 8601，SQL 标准	`1997-12-17 07:37:16-08`
`SQL`	traditional style	`12/17/1997 07:37:16.00 PST`
`Postgres`	original style	`Wed Dec 17 07:37:16 1997 PST`
`German`	regional style	`17.12.1997 07:37:16.00 PST`

Note

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

在 SQL 和 POSTGRES 样式中，如果已指定 DMY 字段 Sequences，则日期显示在月份之前，否则显示日期显示在月份之前。 (有关此设置如何影响 Importing 值的解释，请参见Section 8.5.1。)Table 8.15显示了示例。

表 8.15. 日期 Sequences 约定

`datestyle`设定	Input Ordering	Example Output
`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`

用户可以使用SET datestyle命令，postgresql.conf配置文件中的DateStyle参数或服务器或 Client 端上的PGDATESTYLE环境变量来选择日期/时间样式。

格式化功能to_char(请参阅Section 9.8)也可以作为一种更灵活的方式来格式化日期/时间输出。

8 .5.3. 时区

时区和时区约定受政治决策的影响，而不仅限于地球的几何形状。 1900 年代，世界各地的时区变得有些标准化，但仍然容易发生任意变化，特别是在夏令时方面。 PostgreSQL 使用广泛使用的 IANA(Olson)时区数据库获取有关历史时区规则的信息。对于将来的时间，假设是在给定时区的最新已知规则将 continue 无限远地遵守到将来。

PostgreSQL 努力与典型用法的 SQL 标准定义兼容。但是，SQL 标准将日期和时间类型与功能混合在一起。两个明显的问题是：

尽管date类型不能具有关联的时区，但是time类型可以。除非与日期和时间相关联，否则现实世界中的时区几乎没有意义，因为偏移量会随着夏时制的时限在一年中变化。
默认时区指定为相对 UTC 的恒定数字偏移量。因此，在 DST 边界上进行日期/时间运算时，无法适应夏时制。

为了解决这些困难，建议您在使用时区时使用同时包含日期和时间的日期/时间类型。我们不建议使用类型time with time zone(尽管 PostgreSQL 支持旧应用程序并符合 SQL 标准)。 PostgreSQL 假定您的本地时区为仅包含日期或时间的任何类型。

所有可识别时区的日期和时间都内部存储在 UTC 中。在显示给 Client 端之前，它们会在TimeZone配置参数指定的区域中转换为本地时间。

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

完整的时区名称，例如America/New_York。识别的时区名称在pg_timezone_names视图中列出(请参见Section 52.90)。 PostgreSQL 为此目的使用了广泛使用的 IANA 时区数据，因此其他软件也可以识别相同的时区名称。
时区缩写，例如PST。与全时区名称相反，这样的规范仅定义了与 UTC 的特定偏移量，而全时区名称也可能暗示一组夏时制过渡日期规则。识别的缩写在pg_timezone_abbrevs视图中列出(请参阅Section 52.89)。您不能将配置参数TimeZone或log_timezone设置为时区缩写，但可以在日期/时间 Importing 值中使用缩写，并可以使用AT TIME ZONE运算符。
除时区名称和缩写外，PostgreSQL 还将接受 POSIX 样式的时区规范，格式为* STD * * offset 或 STD * * offset * * DST ，其中 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指定了同一 Moment。但是2014-06-04 12:00 EST指定东部标准时间(UTC-5)中午，无论该日期的夏令时名义上是否生效。

使事情复杂化的是，某些辖区使用了相同的时区缩写来表示不同时间的不同 UTC 偏移量。例如，在莫斯科MSK在某些年份中表示 UTC 3，在其他年份中表示 UTC 4. PostgreSQL 根据指定日期的含义(或最近的含义)解释这些缩写。但是，与上面的EST示例一样，该时间不必与该日期的当地民用时间相同。

应当警惕的是，POSIX 风格的时区功能可能导致静默接受虚假 Importing，因为没有检查时区缩写的合理性。例如，SET TIMEZONE TO FOOBAR0将起作用，并使用 UTC 的一个特有的缩写有效地离开系统。要记住的另一个问题是，在 POSIX 时区名称中，正偏移量用于 Greenwich 的* west 位置。 PostgreSQL 在其他任何地方都遵循 ISO-8601 约定，正时区偏移量是 Greenwich 的东部*。

在所有情况下，时区名称和缩写均不区分大小写。 (这是对 8.2 之前的 PostgreSQL 版本的更改，在某些情况下区分大小写，而在其他情况下则区分大小写.)

时区名称和缩写都不会硬连线到服务器中；它们是从存储在安装目录的.../share/timezone/和.../share/timezonesets/下的配置文件中获得的(请参阅Section B.4)。

可以在文件postgresql.conf或Chapter 19中描述的任何其他标准方式中设置TimeZone配置参数。还有一些特殊的设置方法：

SQL 命令SET TIME ZONE设置会话的时区。这是SET TIMEZONE TO的替代拼写，具有更符合 SQL 规范的语法。
libpqClient 端使用PGTZ环境变量在连接时向服务器发送SET TIME ZONE命令。

8 .5.4. 间隔 Importing

可以使用以下详细语法来编写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时用于输出.)

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

P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

字符串必须以P开头，并且可以包含T，以引入时间单位。可用的单位缩写在Table 8.16中给出。单位可以省略，可以以任何 Sequences 指定，但是小于天的单位必须在T之后出现。特别地，M的含义取决于它在T之前还是之后。

表 8.16. ISO 8601 间隔单位缩写

Abbreviation	Meaning
Y	Years
M	月(在日期部分)
W	Weeks
D	Days
H	Hours
M	分钟(在时间部分)
S	Seconds

在备用格式中：

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 标准，间隔值的所有字段都必须具有相同的符号，因此前导负号适用于所有字段。例如，间隔 Literals'-1 2:03:04'中的负号适用于日期和小时/分钟/秒部分。 PostgreSQL 允许字段具有不同的符号，并且传统上将文本表示形式中的每个字段视为独立签名，因此在此示例中，小时/分钟/秒部分被视为正数。如果IntervalStyle设置为sql_standard，则将前导符号应用于所有字段(但前提是没有其他符号出现)。否则，将使用传统的 PostgreSQL 解释。为避免歧义，如果任何字段为负，建议在每个字段上附加一个显式符号。

在冗长的 Importing 格式中，以及在某些更紧凑的 Importing 格式的字段中，字段值可以包含小数部分；例如'1.5 week'或'01:02:03.45'。此类 Importing 将转换为适当的月数，天数和秒数以进行存储。如果这将导致小数个月或几天，则使用 1 个月= 30 天和 1 天= 24 小时的转换因子将分数添加到低阶字段中。例如，'1.5 month'变成 1 个月零 15 天。在输出中，仅秒将显示为小数。

Table 8.17显示了有效intervalImporting 的一些示例。

表 8.17. 间隔 Importing

Example	Description
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. 间隔输出

使用命令SET intervalstyle可以将间隔类型的输出格式设置为sql_standard，postgres，postgres_verbose或iso_8601四种样式之一。默认值为postgres格式。 Table 8.18显示了每种输出样式的示例。

如果间隔值符合标准的限制(仅年月或仅日间，且不包含正负成分)，则sql_standard样式将生成符合 SQL 标准的间隔 Literals 字符串规范的输出。否则，输出看起来像是标准的年月文本字符串，后跟日文本字符串，并添加了显式符号以消除歧义的混合符号间隔。

DateStyle参数设置为ISO时，postgres样式的输出与 8.4 之前的 PostgreSQL 版本的输出匹配。

当DateStyle参数设置为 non_ISO输出时，postgres_verbose样式的输出与 8.4 之前的 PostgreSQL 版本的输出匹配。

iso_8601样式的输出与 ISO 8601 标准第 4.4.3.2 节中描述的“带有标识符的格式”相匹配。

表 8.18. 间隔输出样式示例

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 年 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

Name	PostgreSQL 中文文档
Version
Language
Badge
Last Updated	2021-07-07T13:04:41