8.5. 日期/时间类型 #
PostgreSQL 支持全套 SQL 日期和时间类型，如表 8.9所示。
表 8.9. 日期/时间类型
| 名字 | 存储尺寸 | 描述 | 低值 | 高值 | 精度 |
|---|---|---|---|---|---|
timestamp [ ( | 8字节 | 日期和时间 | 4713 BC | 5874897 AD | 1微秒 / 14位 |
timestamp [ ( | 8字节 | 包括日期和时间，有时区 | 4713 BC | 5874897 AD | 1微秒 / 14位 |
interval [ ( | 12字节 | 时间间隔 | -178000000年 | 178000000年 | 1微秒 |
date | 4字节 | 仅日期 | 4713 BC | 32767 AD | 1日 |
time [ ( | 8字节 | 仅一天中的时间 | 00:00:00.00 | 23:59:59.99 | 1微秒 |
time [ ( | 12字节 | 仅一天中的时间，带时区 | 00:00:00.00+12 | 23:59:59.99-12 | 1微秒 |
注意
在 PostgreSQL 7.3 之前的版本中，仅写timestamp等效于timestamp with time zone。为了符合 SQL 标准，这一行为已被改变。
time、timestamp 和 interval
都接受一个可选精度值 p，用来指定在秒字段中保留多少位小数。默认情况下，对精度没有显式上界。对于 timestamp 和 interval 类型，p 的允许范围是 0 到 6。
注意
当 timestamp 值以双精度浮点数存储（目前的默认方式）时，精度的有效上限可能小于
6。timestamp 值存储为相对
2000-01-01 午夜之前或之后的秒数。对
2000-01-01 前后几年的日期可以达到微秒精度，但对更远的日期，精度会进一步降低。当
timestamp 值以八字节整数存储（一种编译期选项）时，全部取值范围内都有微秒精度。但八字节整数时间戳的日期范围比上表所示的更有限：从公元前
4713 年到公元 294276 年。
对于 time 类型，采用八字节整数存储时，p 的允许范围是 0 到 6；采用浮点存储时，允许范围是 0 到 10。
time with time zone 类型由 SQL 标准定义，但其定义具有一些会让人怀疑其实用性的特性。在大多数情况下，date、time、timestamp without time zone 和
timestamp with time zone 的组合，就足以提供任何应用所需的完整日期/时间功能。
类型abstime和reltime是内部使用的低精度类型。不推荐在应用中使用这些类型；这些内部类型可能在将来的版本中消失。
8.5.1. 日期/时间输入 #
日期和时间输入几乎可以采用任何合理的格式，包括 ISO
8601、SQL
兼容格式、传统
POSTGRES 格式等。对于某些格式，日期输入中月、日、年的顺序可能有歧义，因此支持指定所期望的这些字段的顺序。将
datestyle 参数设为
MDY 可选择月-日-年解释，设为
DMY 可选择日-月-年解释，设为
YMD 可选择年-月-日解释。
PostgreSQL 在处理日期/时间输入方面，比 SQL 标准要求的更灵活。有关日期/时间输入的精确解析规则，以及可识别的文本字段（包括月份、星期几和时区），请参见附录 B。
请记住，任何日期或时间字面值输入都必须像文本字符串一样用单引号括起来。更多信息请参见第 4.1.2.4 节。SQL 要求使用下列语法：
type[ (p) ] 'value'
其中 p 是可选的精度说明，给出秒字段中保留的小数位数。精度可用于 time、timestamp 和 interval 类型，允许的取值范围见上文。如果在常量声明中没有指定精度，则默认采用该字面值本身的精度。
8.5.1.1. 日期
表 8.10显示了date类型可能的输入方式。
表 8.10. 日期输入
| 示例 | 描述 |
|---|---|
| January 8, 1999 | 在任何datestyle输入模式下都无歧义 |
| 1999-01-08 | ISO 8601；任何模式下的1月8日（推荐格式） |
| 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-Jan-08 | 任何模式下的1月8日 |
| Jan-08-1999 | 任何模式下的1月8日 |
| 08-Jan-1999 | 任何模式下的1月8日 |
| 99-Jan-08 | YMD模式中的1月8日，否则错误 |
| 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 | 儒略日 |
| January 8, 99 BC | 公元前99年 |
8.5.1.2. 时间
一天中的时间类型包括
time [ (
和
p) ] without time zonetime [ (。单独写 p) ] with time zonetime 等效于
time without time zone。
这些类型的有效输入由一个一天中的时间，加上一个可选时区组成（见表 8.11和表 8.12）。如果在
time without 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 |
04:05:06-08:00 | ISO 8601 |
04:05-08:00 | ISO 8601 |
040506-08 | ISO 8601 |
04:05:06 PST | 名称指定的时区 |
表 8.12. 时区输入
| 示例 | 描述 |
|---|---|
PST | 太平洋标准时间 |
-8:00 | PST的ISO-8601偏移 |
-800 | PST的ISO-8601偏移 |
-8 | PST的ISO-8601偏移 |
zulu | UTC的军方缩写 |
z | zulu的缩写形式 |
8.5.1.3. 时间戳
时间戳类型的有效输入由一个日期和一个时间连接而成，其后可跟可选的
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
也被支持。
对于 timestamp [without time zone]，输入中指定的任何显式时区都会被静默忽略。也就是说，得到的日期/时间值由输入值中显式的日期/时间字段导出，不随时区调整。
对于 timestamp with time zone，内部存储的值总是
UTC（Universal
Coordinated Time，传统上称为格林尼治标准时间
GMT）。指定了显式时区的输入值会按该时区的相应偏移转换为
UTC。如果输入串中没有给出时区，则假定它位于系统
timezone 参数指示的时区，并按
timezone 时区的偏移转换为
UTC。
当输出一个 timestamp with time zone 值时，它总会从
UTC 转换到当前 timezone 时区，并显示为该时区的本地时间。若要查看其他时区的时间，可以修改
timezone，或者使用
AT TIME ZONE 构造（见第 9.8.3 节）。
在 timestamp without time zone 和
timestamp with time zone 之间转换时，通常假定
timestamp without time zone 值应被解释为，或输出为，timezone 本地时间。要为该转换指定不同的时区，可以使用 AT TIME ZONE。
8.5.1.4. 间隔
interval值可以用下列语法书写：
[@]quantityunit[quantityunit...] [direction]
其中：quantity是一个数字（可带符号）；unit是second、minute、hour、day、week、month、year、decade、century、millennium，或者这些单位的缩写或复数形式；direction可以是ago或为空。at 符号（@）是可选的噪音。不同单位的数量会带着适当的符号被隐式地累加起来。
天、小时、分钟和秒的数量可以不带显式的单位标记来指定。例如，'1 12:59:10'的读法与
'1 day 12 hours 59 min 10 sec'相同。
可选的精度p应当在 0 到 6 之间，默认为输入字面量的精度。
8.5.1.5. 特殊值
下面的函数与 SQL 兼容，可用作相应数据类型的日期或时间值：CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP。后四个函数接受可选的精度说明。（另见第 9.8.4 节。）
PostgreSQL 还为方便使用支持几种特殊日期/时间输入值，如表 8.13所示。值
infinity 和 -infinity
在系统内部有特殊的表示并以相同方式显示；其余的只是记法上的简写，读取时会被转换为普通的日期/时间值。所有这些值都被当作普通常量处理，需要写成单引号括起来的形式。
表 8.13. 特殊日期/时间输入
| 输入字符串 | 有效类型 | 描述 |
|---|---|---|
epoch | date, timestamp | 1970-01-01 00:00:00+00（Unix系统时间0） |
infinity | timestamp | 晚于所有其他时间戳 |
-infinity | timestamp | 早于所有其他时间戳 |
now | date, time, timestamp | 当前事务的开始时间 |
today | date, timestamp | 今天午夜 |
tomorrow | date, timestamp | 明天午夜 |
yesterday | date, timestamp | 昨天午夜 |
allballs | time | 00:00:00.00 UTC |
8.5.2. 日期/时间输出 #
日期/时间类型的输出格式可以设为四种样式之一：ISO 8601、SQL（Ingres）、传统的 POSTGRES 和 German，这通过命令SET datestyle设置。默认是ISO格式。（SQL标准要求使用 ISO 8601 格式。之所以存在名为“SQL”的输出格式，只是历史原因。）表 8.14展示了各种输出样式的示例。date和time类型的输出当然只包含给定示例中相应的日期部分或时间部分。
表 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 |
| German | 地区样式 | 17.12.1997 07:37:16.00 PST |
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 |
interval 的输出与输入格式相似，区别在于像
century 或
wek 这样的单位会被转换为年和天，并且
ago 会被转换为适当的符号。在
ISO 模式下输出形如
[quantityunit[ ... ] ] [days] [hours:minutes:sekunden]
用户可以用 SET datestyle 命令、postgresql.conf 配置文件中的
datestyle 参数，或服务器/客户端上的
PGDATESTYLE 环境变量来选择日期/时间风格。格式化函数
to_char（见第 9.7 节）也可作为一种更灵活的日期/时间输出格式化手段。
8.5.3. 时区 #
时区及时区惯例受政治决策的影响，而不只是地球几何。世界各地的时区在
20 世纪期间变得多少标准化了一些，但仍容易发生任意的变更。PostgreSQL
使用操作系统的底层特性来提供输出时区支持，而这些系统通常只包含
1902 到 2038 年期间的信息（对应传统 Unix
系统时间的全部范围）。timestamp with time zone 和
time with time
zone 只在该年份范围内使用时区信息，并假定范围外的时间位于
UTC。不过由于时区支持源自底层操作系统的时区能力，它可以处理夏令时和其他特殊行为。
PostgreSQL 力求在典型用法上与 SQL 标准定义兼容。但 SQL 标准对日期和时间类型和能力方面有一种奇怪的组合。两个明显的问题是：
尽管
date类型没有关联的时区，time类型却可以有关联的时区。现实世界中的时区若不同时关联日期和时间就可能没有意义，因为偏移量在一年中可能随夏令时边界而变化。默认时区被指定为相对 UTC 的固定数字偏移。在跨 DST 边界进行日期/时间运算时无法适应夏令时。
为解决这些困难，我们建议在使用时区时选用同时包含日期和时间的日期/时间类型。我们建议不要使用
time with
time zone 类型（尽管为了旧应用以及与其他
SQL 实现的兼容，PostgreSQL
支持它）。对于只包含日期或只包含时间的类型，PostgreSQL
假定使用你的本地时区。
所有日期和时间在内部都以 UTC 存储。时间在发送给客户端之前会被转换为数据库服务器上的本地时间，因此默认情况下采用服务器时区。
有几种方法可以选择服务器使用的时区：
如果没有指定其他时区，服务器主机上的
TZ环境变量将被服务器用作默认时区。timezone配置参数可以在文件postgresql.conf中设置。libpq客户端使用
PGTZ环境变量在连接时向服务器发送SET TIME ZONE命令。SQL 命令
SET TIME ZONE设置会话的时区。
注意
如果指定了无效的时区，时区会变成 UTC（至少在大多数系统上如此）。
有关可用时区的列表，请参见附录 B。
8.5.4. 内部实现 #
PostgreSQL在所有日期/时间计算中使用儒略日。这带来一个有用的特性：在假定一年长度为 365.2425 天的前提下，能够正确计算从公元前 4713 年到遥远未来的日期。
19 世纪之前的日期约定读起来很有意思，但它们并不足够一致，不值得写进日期/时间处理器中。