在 PostgreSQL 中使用日期和时间

作者：Josh Berkus

本 FAQ 旨在回答以下问题

问：PostgreSQL 中的 DATEADD() 和 DATEDIFF() 函数在哪里？
问：如何计算 X 和 Y 之间的时间量？

关键词：日期，日期时间，时间戳，运算符，dateadd，datediff，间隔

首先，法律声明

版权 2001 Josh Berkus (http://www.agliodbs.com)。 只要此版权声明出现在文档中，或在任何付费出版物中（如果此类出版物的收益的 1% 或更多用于捐赠或支付给 PostgreSQL 开发），则允许在任何不收取费用的公共论坛中使用。 此建议不提供任何形式的保证，包括针对特定目的的适用性保证。 使用风险自负。

介绍

PostgreSQL 的一大优势是它对各种日期和时间数据类型及其相关运算符提供了强大的支持。 这使我能够在 PostgreSQL 中编写日历应用程序，而这些应用程序在其他平台上会非常困难。

在深入研究细节之前，我需要向许多来自数据库应用程序（其 ANSI 92 SQL 兼容性不如 PostgreSQL，特别是 Microsoft SQL Server、SyBase 和 Microsoft Access）的人解释一些事情。 如果你已经了解这些知识，可以跳到“使用日期时间、日期和间隔值”部分。

（顺便说一下，这里不是针对微软的攻击。 我用 MS SQL Server 作为非标准兼容数据库的例子，因为我是经过认证的 MS SQL Server 管理员，并且非常了解它的问题。 市场上还有很多其他非兼容数据库。）

ANSI SQL 和运算符

在 ANSI SQL 世界中，运算符（如 + - * % || !）仅在操作数据类型的上下文中定义。 因此，两个整数的除法（INT / INT）与两个浮点数的除法（FLOAT / FLOAT）的功能不同。 更戏剧性的是，你可以从另一个整数中减去一个整数（INT - INT），但不能从另一个字符串中减去一个字符串（VARCHAR - VARCHAR），更不用说从整数中减去一个字符串了（INT - VARCHAR）。 这两个运算中的减法运算符 (-) 虽然看起来相同，但实际上是不同的，因为它们处于不同的数据类型上下文中。 在没有预定义上下文的情况下，运算符根本不起作用，你会收到错误消息。

这条基本规则有一些繁琐的后果。 经常需要将两个值强制转换为相同的数据类型才能对它们进行操作。 例如，尝试添加一个 FLOAT 值和一个 NUMERIC 值； 除非将它们都定义为 FLOAT 或 NUMERIC（CAST(FLOAT AS NUMERIC) + NUMERIC），否则你会收到错误消息。 更重要的是，将一个整数追加到字符串的末尾需要一个类型转换函数（to_char(INT, '00000')）。 此外，如果你想定义自己的数据类型，你还需要花费数小时来为它们定义所有可能的运算符。

一些数据库开发人员急于将他们的产品推向市场，看到了上述“对用户不友好”的行为，并通过定义所有运算符以上下文无关的方式来消除它。 因此，在 Microsoft Transact-SQL 中，你可以添加一个 DOUBLE 值和一个 INTEGER 值，或者在某些情况下甚至可以直接将一个 INTEGER 值追加到一个字符串。 数据库可以为你处理隐式转换，因为它们已经被简化了。

但是，Transact-SQL 开发人员忽略了将上下文敏感运算符包含到 SQL 标准中的本质原因。 只有使用真正的上下文敏感运算符，你才能处理不遵循算术或连接规则的特殊数据类型。 PostgreSQL 处理 IP 地址、几何形状以及最重要的是我们讨论的日期和时间的能力取决于这种强大的运算符实现。 非兼容的 SQL 方言，如 Transact-SQL，被迫使用专有的函数，如 DATEADD() 和 DATEDIFF() 来处理日期和时间，并且根本无法处理更复杂的数据类型。

因此，为了回答第一个问题 

问：PostgreSQL 中的 DATEADD 和 DATEDIFF 函数在哪里？
答：不存在。 PostgreSQL 不需要它们。 改用 + 和 - 运算符。 继续阅读。

使用日期时间、日期和间隔值

有关日期/时间数据类型的完整文档 已经存在，我不会尝试在这里复制它们。 相反，我将尝试向初学者解释你需要知道哪些知识才能真正使用日期、时间和间隔。

类型

日期时间或时间戳

结构化的“真实”日期和时间值，包含所有有用日期和时间值的年、月、日、时、分、秒和毫秒（公元前 4713 年到公元后 100,000 多年）。

日期

日期的简化基于整数的表示形式，仅定义年、月和日。

间隔

表示时间段的结构化值，包括年、月、周、日、时、分、秒和毫秒中的任何/所有值。“1 天”、“42 分钟 10 秒”和“2 年”都是间隔值。

时间戳带时区呢？

这是一个重要的话题，我不想在这里讨论。 最终会有人对此进行记录。 只要知道所有时间戳值都包含时区数据，如果你不需要处理不同的时区，可以安全地忽略它。

我应该使用 DATE 还是 TIMESTAMP？ 我不需要分钟或小时

这取决于具体情况。 DATE 在算术运算方面更容易使用（例如，以随机天数间隔重复出现的事件），占用更少的存储空间，并且在打印时不会拖尾不需要的“00:00:00”字符串。 但是，TIMESTAMP 更适合真正的日历计算（例如，每月 15 日或闰年的第二个星期四发生的事件）。 下面将进一步介绍。

现在，要使用时间戳和间隔，你需要了解以下几个简单的规则 

1. 两个时间戳之间的差值始终是一个间隔

TIMESTAMP '1999-12-30' - TIMESTAMP '1999-12-11' = INTERVAL '19 days'

2. 你可以向时间戳添加或减去一个间隔，从而产生另一个时间戳

TIMESTAMP '1999-12-11' + INTERVAL '19 days' = TIMESTAMP '1999-12-30'

3. 你可以添加或减去两个间隔

INTERVAL '1 month' + INTERVAL '1 month 3 days' = INTERVAL '2 months 3 days'

4. 间隔的乘法和除法目前正在开发和讨论中

建议你在实现完成之前避免使用它，否则你可能会得到意外的结果。

由于并非所有间隔的除法都能得到明确的答案，因此它可能永远不会实现。 如果你只希望除以包含日、时、分、秒的间隔（如时间戳减法的结果），则可以使用以下方法

 EXTRACT(EPOCH FROM INTERVAL '4 hours') / EXTRACT(EPOCH FROM INTERVAL '2 hours') = 2

5. 你不能（永远不能）对两个时间戳执行加、乘或除运算

TIMESTAMP '2001-03-24' + TIMESTAMP '2001-10-01' = OPERATION ERROR

6. 许多较大的间隔值，如它们所反映的日历值，在表示为较小的间隔值时不是恒定的

例如（差异以粗体显示）

TIMESTAMP '2001-07-02' + INTERVAL '1 month' = TIMESTAMP '2001-08-02'
TIMESTAMP '2001-07-02' + INTERVAL '31 days' = TIMESTAMP '2001-08-02'

但

TIMESTAMP '2001-02-02' + INTERVAL '1 month' = TIMESTAMP '2001-03-02'
TIMESTAMP '2001-02-02' + INTERVAL '31' days' = TIMESTAMP '2001-03-05'

这使得时间戳/间隔组合非常适合用于调度必须每月 8 日重复发生的事件，而不管月份的长度如何，但如果你试图计算过去 3.5 个月的日数，则会导致问题。 请牢记这一点！

但是，DATE 数据类型在处理方面更简单，尽管功能不那么强大。

对日期的操作

1. 两个日期之间的差值始终是一个整数，表示日期差的日数

DATE '1999-12-30' - DATE '1999-12-11' = INTEGER 19

您可以将整数加减到日期以生成另一个日期。

DATE '1999-12-11' + INTEGER 19 = DATE '1999-12-30'

DATE/INTEGER 不能计算出月份和年份的长度变化。

由于日期差值始终计算为整天的数量，因此 DATE/INTEGER 无法计算出月份和年份的长度变化。因此，您无法使用 DATE/INTEGER 将某个事件安排在每个月的 5 号，除非您实时进行非常复杂的月份长度计算。这使得 DATE 非常适合需要大量基于天数计算的日历应用程序（例如“员工‘x’已经工作了多少个 14 天周期？”），但不适合实际的日历应用程序。请记住这一点。

我正在将一个应用程序从 MS SQL Server 移植，我需要支持 DATEDIFF 和 DATEADD 函数，以便我的存储视图能够正常工作。

请访问 PostgreSQL 技术文档 (http://techdocs.postgresql.org)。那里有很多移植资源，如果有人已经用 PostgreSQL 重新创建了这些函数，我不会感到惊讶。

我需要将日期显示为文本，或者将文本转换为日期或时间间隔。

您需要 to_date()、to_char() 和 interval() 函数。

如果我想从日期中获取月份作为整数，该怎么办？

您需要 extract() 函数。此函数还可以从时间戳中获取其他数字间隔，包括 Unix 系统日期时间（例如 EXTRACT (epoch from some_date)）。