PostgreSQL 8.2.3 中文文档
后退快退章8. Data Types快进前进

8.5. 日期/时间类型

表8-9显示了 PostgreSQL 支持的 SQL 中所有日期和时间类型。这些数据类型上可以进行的操作在节9.9中描述。

表8-9. 日期/时间类型

名字存储空间描述最低值最高值分辨率
timestamp [ (p) ] [ without time zone ]8 字节日期和时间4713 BC5874897 AD1 毫秒 / 14 位
timestamp [ (p) ] with time zone8 字节日期和时间,带时区4713 BC5874897 AD1 毫秒 / 14 位
interval [ (p) ]12 字节时间间隔-178000000 年178000000 年1 毫秒 / 14 位
date4 字节只用于日期4713 BC5874897 AD1 天
time [ (p) ] [ without time zone ]8 字节只用于一日内时间00:00:0024:00:001 毫秒 / 14 位
time [ (p) ] with time zone12 字节只用于一日内时间,带时区00:00:00+145924:00:00-14591 毫秒 / 14 位

【注意】在 PostgreSQL 7.3 以前,只写 timestamp 等效于 timestamp with time zone 。这是和 SQL 不兼容的。

time, timestamp, interval 接受一个可选的精度值 p 以指明秒域中小数部分的位数。没有明确的缺省精度,p 的范围对 timestampinterval 类型是从 0 到大约 6 。

【注意】如果 timestamp 数值是以双精度浮点数(目前的缺省)的方式存储的,那么有效精度会小于 6 。timestamp 值是以 2000-01-01 午夜之前或之后的秒数存储的,而微秒的精度是为那些在 2000-01-01 前后几年的日期实现的,对于那些远一些的日子,精度会下降。如果timestamp 以八字节整数存储(一个编译时的选项),那么微秒的精度就可以在数值的全部范围内都可以获得。不过,八位整数的时间戳范围缩小到 4713 BC 到 294276 AD 之间。同一个编译时选项也决定 timeinterval 值是保存成浮点数还是八字节整数。在以浮点数存储的时候,随着时间间隔的增加,大的 interval 数值的精度会降低。

对于 time 类型,如果使用了八字节的整数存储,那么 p 允许的范围是从 0 到 6 ,如果使用的是浮点数存储,那么这个范围是 0 到 10 。

time with time zone 类型是 SQL 标准定义的,但是完整定义的有些方面会导致有问题的用法。在大多数情况下,date, time, timestamp without time zone, timestamp with time zone 的组合就应该能提供一切应用需要的日期/时间的完整功能。

abstimereltime 类型是低分辨率类型,它们被用于系统内部。我们反对你使用这些类型,因为这些旧类型的部分或全部可能会在未来的版本里消失。

8.5.1. 日期/时间输入

日期和时间的输入几乎可以是任何合理的格式,包括 ISO-8601 格式、SQL-兼容格式、传统 POSTGRES 格式、其它的形式。对于一些格式,日期输入里的月和日可能会让人迷惑,因此系统支持自定义这些字段的顺序。把 DateStyle 参数设置为 MDY 就按照"月-日-年"解析,设置为 DMY 就按照"日-月-年"解析,设置为 YMD 就按照"年-月-日"解析。

PostgreSQL 在处理日期/时间输入上比 SQL 标准要求的更灵活。参阅附录B获取关于日期/时间输入的准确分析规则和可识别文本字段,包括月份、星期几、时区。

请记住任何日期或者时间的文本输入需要由单引号包围,就像一个文本字符串一样。参考节4.1.2.5获取更多信息。SQL 要求使用下面的语法:

type [ (p) ] 'value'

可选的精度声明中的 p 是一个整数,表示在秒域中小数部分的位数,我们可以对 time, timestamp, interval 类型声明精度。允许的精度在上面已经说明。如果在常量声明中没有声明精度,缺省是文本值的精度。

8.5.1.1. 日期

表8-10显示了 date 类型可能的输入方式。

表8-10. 日期输入

例子描述
January 8, 1999在任何 DateStyle 输入模式下都无歧义
1999-01-08ISO 8601 格式(建议格式),任何方式下都是 1999 年 1 月 8 号
1/8/1999有歧义,在 MDY 下是一月八号;在 DMY 模式下是八月一日
1/18/1999MDY 模式下是一月十八日,其它模式下被拒绝
01/02/03MDY 模式下的 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-08YMD 模式下是 1 月 8 日,否则错误
08-Jan-99一月八日,除了在 YMD 模式下是错误的之外
Jan-08-99一月八日,除了在 YMD 模式下是错误的之外
19990108ISO 8601;任何模式下都是 1999 年 1 月 8 日
990108ISO 8601;任何模式下都是 1999 年 1 月 8 日
1999.008年和年里的第几天
J2451187儒略日
January 8, 99 BC公元前 99 年

8.5.1.2. 时间

当日时间类型是 time [ (p) ] without time zonetime [ (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.789ISO 8601
04:05:06ISO 8601
04:05ISO 8601
040506ISO 8601
04:05 AM与 04:05 一样;AM 不影响数值
04:05 PM与 16:05 一样;输入小时数必须 <= 12
04:05:06.789-8ISO 8601
04:05:06-08:00ISO 8601
04:05-08:00ISO 8601
040506-08ISO 8601
04:05:06 PST缩写的时区
2003-04-12 04:05:06 America/New_York用名字声明的时区

表8-12. 时区输入

例子描述
PST太平洋标准时间(Pacific Standard Time)
America/New_York完整时区名称
PST8PDTPOSIX 风格的时区
-8:00ISO-8601 与 PST 的偏移
-800ISO-8601 与 PST 的偏移
-8ISO-8601 与 PST 的偏移
zulu军方对 UTC 的缩写(译注:可能是美军)
zzulu 的缩写

参考节8.5.3 以获取如何指定时区的更多信息。

8.5.1.3. 时间戳

时间戳类型的有效输入由一个日期和时间的连接组成,后面跟着一个可选的时区,一个可选的 ADBC 。另外,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

SQL 标准通过"+"或者"-"是否存在来区分 timestamp without time zonetimestamp 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.3)。

timestamp without time zonetimestamp with time zone 之间的转换通常假设 timestamp without time zone 数值应该以 timezone 本地时间的形式接受或者写出。其它的时区引用可以用 AT TIME ZONE 的方式为转换声明。

8.5.1.4. 时间间隔

interval 数值可以用下面的语法声明:

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

这里:quantity 是一个数字(可能有符号);unitsecond, minute, hour, day, week, month, year, decade, century, millennium 或者这些单位的缩写或复数;direction 可以是 ago 或者为空。@ 符号是一个可选的东西。不同的单位以及相应正确的符号都是隐含地增加的。

日期、小时、分钟、秒钟的数量可以在无明确单位标记的情况下声明。比如,'1 12:59:10''1 day 12 hours 59 min 10 sec' 读数一样。

可选的精度 p 应该介于 0 和 6 之间,并且缺省是输入文本的精度。

在内部,interval 的值按照月、日、秒存储。这样做是恰当的,因为每个月的天数不同,并且每天可能有 23 或 25 小时(涉及夏令时调整时)。因为时间间隔通常是从字符串常量或 timestamp 之差创建的,这种存储方法在大多数情况下更加合理。justify_daysjustify_hours 函数用于调整溢出的日期和小时。

8.5.1.5. 特殊值

PostgreSQL 为方便起见支持在表8-13里面显示的几个特殊输入值。值 infinity-infinity 是特别在系统内部表示的,并且将按照同样的方式显示;但是其它的都只是符号缩写,在读取的时候将被转换成普通的日期/时间值。特别是 now 和相关的字符串在读取的时候就被转换成对应的数值。所有这些值在 SQL 命令里当作普通常量对待时,都需要写在单引号里面。

表8-13. 特殊日期/时间输入

输入字符串适用类型描述
epochdate, timestamp1970-01-01 00:00:00+00 (Unix 系统零时)
infinitytimestamp比任何其它时间戳都晚
-infinitytimestamp比任何其它时间戳都早
nowdate, time, timestamp当前事务的开始时间
todaydate, timestamp今日午夜
tomorrowdate, timestamp明日午夜
yesterdaydate, timestamp昨日午夜
allballstime00:00:00.00 UTC

下列 SQL 兼容函数也可以用于获取对应数据类型的当前时间值:CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP 。后四个接受一个可选的精度声明(节9.9.4)。不过,请注意这些 SQL 函数不是被当作数据输入字符串识别的。

8.5.2. 日期/时间输出

日期/时间类型的输出格式可以使用 SET datestyle 设成 ISO 8601(默认)、SQL(Ingres)、传统的 POSTGRES 、German 四种风格之一。SQL 标准要求使用 ISO 8601 格式。"SQL"输出格式的名字是历史偶然。表8-14显示了每种输出风格的例子。datetime 类型的输出当然只是给出的例子里面的日期和时间部分。

表8-14. 日期/时间输出风格

风格描述例子
ISOISO 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

如果声明了 DMY 顺序,那么在 SQL 和 POSTGRES 风格里,日期在月份之前出现,否则月份出现在日期之前(参阅节8.5.1看看这个设置如何影响对输入值的解释)。表8-15显示了一个例子。

表8-15. 日期顺序习惯

datestyle 设置输入顺序输出样例
SQL, DMY//17/12/1997 15:37:16.00 CET
SQL, MDY//12/17/1997 07:37:16.00 PST
Postgres, DMY//Wed 17 Dec 07:37:16 1997 PST

interval 的输出看起来像输入格式,只是像 centuryweek 这样的单位被转换成年和日,而 ago 被转换成合适的符号。在 ISO 模式下输出看起来像

[quantity unit [...]] [days] [hours:minutes:seconds]

用户可以用 SET datestyle 命令选取日期/时间的风格,也可以在配置文件 postgresql.conf 中的 DateStyle 参数中设置,或者在服务器或客户端的 PGDATESTYLE 环境变量中设置。我们也可以用格式化函数 to_char(参见节9.8)来更灵活地控制时间/日期地输出。

8.5.3. 时区

时区和时区习惯不仅仅受地球几何形状的影响,还受到政治决定的影响。到了 19 世纪,全球的时区变得稍微标准化了些,但是还是易于遭受随意的修改,部分是因为夏时制规则。PostgreSQL 目前支持 1902 年到 2038 年之间的夏时制信息(对应于传统 Unix 系统时间的完整跨度)。如果时间超过这个范围,那么假设时间是所选时区的"标准时间",不管它们落在哪个年份里面。

PostgreSQL 在典型应用中尽可能与 SQL 的定义相兼容。但 SQL 标准在日期/时间类型和功能上有一些奇怪的混淆。两个显而易见的问题是:

为了克服这些困难,我们建议在使用时区的时候,使用那些同时包含日期和时间的日期/时间类型。我们建议不要使用 time with time zone 类型(尽管 PostgreSQL 出于合理应用以及为了与其它 RDBMS 兼容的考虑支持这个类型)。PostgreSQL 假设你用于任何类型的本地时区都只包含日期或时间(而不包含时区)。

在系统内部,所有日期和时间都用全球统一时间 UTC 格式存储,时间在发给客户前端前由数据库服务器根据 timezone 配置参数声明的时区转换成本地时间。

PostgreSQL 允许你用三种方法指定时区:

完整的时区名与时区缩写在理论与实践之间存在差异:时区缩写总是代表一个相对于 UTC 的固定偏移量,然而大多数完整的时区名隐含着一个本地夏令时规则,因此就有可能有两个相对于 UTC 的不同偏移量。

总体而言,PostgreSQL 8.2 版本以后时区名在所有情况下都是大小写无关的。而之前的版本在某些情况下是大小写敏感的。

无论是完整的时区名还是时区缩写都不是硬连接进服务器的,它们都是从安装目录下的 .../share/timezone/.../share/timezonesets/ 配置文件中获取的(参见章B.3)

可以在 postgresql.conf 文件里设置 timezone 配置参数,或者用任何其它在章17描述的标准方法。除此之外,还有好几种特殊方法可以设置它:

8.5.4. 内部

PostgreSQL 使用儒略历法计算所有日期/时间,假设一年的长度是 365.2425 天。这个方法可以很精确地预计/计算从 4713 BC(公元前 4713 年)到很久的未来的任意一天的日期。

19 世纪以前的日期传统(历法)只是对一些趣味读物有意义,而在我们这里好像没有充分的理由把它们编码入日期/时间控制器里面去。


后退首页前进
二进制数据类型上一级布尔类型