3.4. 日期/时间类型 #
Postgres 支持全套 SQL 日期和时间类型。
表 3.6. 日期/时间类型
| 类型 | 描述 | 存储尺寸 | 最早 | 最晚 | 精度 |
|---|---|---|---|---|---|
timestamp | 日期和时间 | 8字节 | 4713 BC | AD 1465001 | 1微秒 / 14位 |
timestamp [ with time zone ] | 带时区的日期和时间 | 8字节 | 1903 AD | 2037 AD | 1微秒 / 14位 |
interval | 用于时间间隔 | 12字节 | -178000000年 | 178000000年 | 1微秒 |
date | 仅日期 | 4字节 | 4713 BC | 32767 AD | 1日 |
time [ without time zone ] | 仅一天中的时间 | 4字节 | 00:00:00.00 | 23:59:59.99 | 1微秒 |
time with time zone | 仅一天中的时间 | 4字节 | 00:00:00.00+12 | 23:59:59.99-12 | 1微秒 |
注意
为确保与
Postgres
早期版本的兼容，我们还继续提供
datetime（等价于
timestamp）和
timespan（等价于
interval），但对它们的支持现在仅限于到
timestamp 和
interval
的隐式转换。abstime
和 reltime
类型是内部使用的低精度类型。不鼓励在新应用中使用这些类型，并建议在合适时把旧的迁移过去。这些内部类型中的任何一种或全部都可能在未来的版本中消失。
3.4.1. 日期/时间输入 #
日期和时间输入几乎可以采用任何合理的格式，包括
ISO-8601、SQL
兼容格式、传统
Postgres 格式等。日期输入中月和日的顺序可能有歧义，因此有一个设置用于指定歧义情况下如何解释。命令
SET DateStyle TO 'US' 或
SET DateStyle TO 'NonEuropean'
指定"月在前"的变体，命令
SET DateStyle TO 'European'
则设置"日在前"的变体。ISO
风格是默认值，但这一默认可以在编译时或运行时改变。
关于日期/时间输入的精确解析规则和可识别的时区，参见附录 A。
请记住，任何日期或时间输入都必须像文本字符串一样用单引号括起来。更多信息请参见第 1.1.2.5 节。SQL 要求使用下列语法
type'value'
但 Postgres 更灵活。
3.4.1.1. date
下面是 date 类型的一些可能输入。
表 3.7. 日期输入
| 示例 | 描述 |
|---|---|
| January 8, 1999 | 无歧义 |
| 1999-01-08 | ISO-8601 格式，推荐 |
| 1/8/1999 | 美式；在欧洲模式下读作 8 月 1 日 |
| 8/1/1999 | 欧式；在美式模式下读作 8 月 1 日 |
| 1/18/1999 | 美式；在任何模式下都读作 1 月 18 日 |
| 19990108 | ISO-8601 年、月、日 |
| 990108 | ISO-8601 年、月、日 |
| 1999.008 | 年和一年中的日子 |
| 99008 | 年和一年中的日子 |
| January 8, 99 BC | 公元前的 99 年 |
表 3.8. 月份缩写
| 月份 | 缩写 |
|---|---|
| April | Apr |
| August | Aug |
| December | Dec |
| February | Feb |
| January | Jan |
| July | Jul |
| June | Jun |
| March | Mar |
| November | Nov |
| October | Oct |
| September | Sep, Sept |
注意
出于显而易见的原因，五月（May）没有明确的缩写。
表 3.9. 星期缩写
| 日 | 缩写 |
|---|---|
| Sunday | Sun |
| Monday | Mon |
| Tuesday | Tue, Tues |
| Wednesday | Wed, Weds |
| Thursday | Thu, Thur, Thurs |
| Friday | Fri |
| Saturday | Sat |
3.4.1.2. time [ without time zone ]
按 SQL99，此类型可以写作
time 和
time without time zone。
下面是有效的 time 输入。
表 3.10. 时间输入
| 示例 | 描述 |
|---|---|
| 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 |
| z | 和 00:00:00 一样 |
| zulu | 和 00:00:00 一样 |
| allballs | 和 00:00:00 一样 |
3.4.1.3. time with time zone
此类型由 SQL92
定义，但该定义存在根本性的缺陷，使这一类型几乎无法使用。在大多数情况下，date、time 和
timestamp
的组合应能提供任何应用所需的完整日期/时间功能。
time with time zone 接受所有对
time 类型合法的输入，再加上一个合法的时区，如下：
表 3.11. 带时区的时间输入
| 示例 | 描述 |
|---|---|
| 04:05:06.789-8 | ISO-8601 |
| 04:05:06-08:00 | ISO-8601 |
| 04:05-08:00 | ISO-8601 |
| 040506-08 | ISO-8601 |
更多时区的示例参见表 3.12。
3.4.1.4. timestamp
timestamp
类型的有效输入由一个日期和一个时间连接而成，其后可跟可选的
AD 或
BC，再后可跟可选的时区。（见下文。）因此
1999-01-08 04:05:06 -8:00
是一个符合 ISO
的有效 timestamp 值。此外，广泛使用的格式
January 8 04:05:06 1999 PST
也被支持。
表 3.12. 时区输入
| 时区 | 描述 |
|---|---|
| PST | 太平洋标准时间 |
| -8:00 | PST的ISO-8601偏移 |
| -800 | PST的ISO-8601偏移 |
| -8 | PST的ISO-8601偏移 |
3.4.1.5. interval
interval 可用下列语法指定：
Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Direction]
其中：Quantity（数量）是 ...、-1、0、1、2、...；Unit（单位）是 second、minute、hour、day、week、month、year、decade、century、millennium，或者这些单位的缩写或复数形式；Direction（方向）可以是 ago 或为空。
3.4.1.6. 特殊值
下面的函数与
SQL 兼容，可用作相应数据类型的日期或时间输入：CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP。
Postgres 还为方便使用支持几种特殊常量。
表 3.13. 特殊日期/时间常量
| 常量 | 描述 |
|---|---|
| current | 当前事务时间，延迟求值 |
| epoch | 1970-01-01 00:00:00+00（Unix系统时间0） |
| infinity | 晚于其他有效时间 |
| -infinity | 早于其他有效时间 |
| invalid | 非法输入 |
| now | 当前事务时间 |
| today | 今天午夜 |
| tomorrow | 明天午夜 |
| yesterday | 昨天午夜 |
'now'
在插入值时解析，而
'current'
在每次取值时解析。所以你可能想用
'now'
在大多数应用中。（当然，你真正想用的是
CURRENT_TIMESTAMP，它等价于
'now'。）
3.4.2. 日期/时间输出 #
输出格式可以用
SET DateStyle
设为四种风格之一：ISO-8601、SQL（Ingres）、传统
Postgres 和德式。默认是 ISO 格式。
表 3.14. 日期/时间输出风格
| 样式说明 | 描述 | 示例 |
|---|---|---|
| 'ISO' | ISO-8601 标准 | 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 |
date 和
time 风格的输出当然只是上述示例中相应的日期或时间部分。
SQL 风格有欧式和非欧式（美式）两种变体，它们决定月跟在日后还是日跟在月后。（另见上文一节中关于此设置如何影响输入值解释的说明。）
表 3.15. 日期顺序习惯
| 样式说明 | 描述 | 示例 |
|---|---|---|
| European | day/month/year | 17/12/1997 15:37:16.00 MET |
| US | month/day/year | 12/17/1997 07:37:16.00 PST |
interval 的输出与输入格式相似，区别在于像
week 或
century 这样的单位会被转换为年和天。ISO
模式下输出形如
[ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ]
有几种方法可以影响日期/时间类型的外观：
postmaster 启动时后端直接使用的
PGDATESTYLE环境变量。会话启动时前端 libpq 使用的
PGDATESTYLE环境变量。SET DATESTYLESQL 命令。
3.4.3. 时区 #
Postgres 力求在典型用法上与 SQL92 定义兼容。但 SQL92 标准对日期和时间类型及能力有一种奇怪的组合。两个明显的问题是：
尽管
date类型没有关联的时区，time类型却可以或确实有关联的时区。现实世界中的时区若不同时关联日期和时间就可能没有意义，因为偏移量在一年中可能随夏令时边界而变化。默认时区被指定为相对 GMT/UTC 的固定整数偏移。在跨 DST 边界进行日期/时间运算时无法适应夏令时。
为解决这些困难，我们建议在使用时区时选用同时包含日期和时间的日期/时间类型。我们建议不要使用 SQL92 的 TIME WITH TIME ZONE（尽管为了旧应用以及与其他 RDBMS 实现的兼容，Postgres 支持它）。对于只包含日期或只包含时间的类型，Postgres 假定使用本地时间。此外，时区支持源自底层操作系统的时区能力，因此可以处理夏令时和其他预期行为。
对于 1902 到 2038 年之间的日期，Postgres 从底层操作系统获得时区支持（接近 Unix 风格系统的典型日期界限）。在此范围之外，所有日期都假定以协调世界时（UTC）指定和使用。
所有日期和时间在内部都以 UTC（传统上称为格林尼治标准时间 GMT）存储。时间在发送给客户端前端之前会被转换为数据库服务器上的本地时间，因此默认情况下采用服务器时区。
有几种方法可以影响时区行为：
postmaster 启动时后端直接使用 TZ 环境变量作为默认时区。
在客户端设置的 PGTZ 环境变量由 libpq 用于在连接时向后端发送时区信息。
SQL 命令
SET TIME ZONE设置会话的时区。SQL92 的限定词
timestampAT TIME ZONE 'zone'其中
zone可以指定为文本时区（例如'PST'）或区间（例如INTERVAL '-08:00'）。
注意
如果指定了无效的时区，时区会变成 GMT（至少在大多数系统上如此）。
注意
如果设置了编译选项
USE_AUSTRALIAN_RULES，那么
EST 指的是澳大利亚东部标准时间，其偏移为相对 UTC +10:00 小时。
3.4.4. 内部实现 #
Postgres 在所有日期/时间计算中使用儒略日。它们有一个好的特性：在假定一年长度为 365.2425 天的前提下，能正确预测/计算从公元前 4713 年到遥远未来的任何日期。
19 世纪之前的日期约定读起来很有意思，但它们并不足够一致，不值得写进日期/时间处理器中。