postgresql - 将 PostgreSQL 间隔转换为秒会产生错误的值

Question

我在 Postgresql 中使用一些间隔，但我不确定我是否理解间隔是如何工作的。

我想做的是将间隔转换为秒。所以我有以下值:

select extract ('epoch' from '1 year'::INTERVAL);

生成数字 31557600。如果我们将此数字除以一天中的秒数 (60*60*24)，我们将得到 365。
所以 postgresql 间隔中的一年有 365 天。

select extract ('epoch' from '1 month'::INTERVAL);

产生数字 2592000。如果我们将这个数字除以 (60*60*24)，我们得到 30。
所以在 postgresql 间隔中一个月有 30 天。

所以按照这个逻辑，我正在尝试计算以下间隔中的秒数:

select extract ('epoch' from '2 year 2 month 1 day 10 hour 5 minute'::INTERVAL);

我使用以下公式计算上述查询的结果:

SELECT (years * 365 * 24 * 3600) + (months * 30 * 24 * 3600) + (days * 24 * 3600) + (hours * 3600) + (minutes * 60);

当我们用值替换变量时，表达式如下所示:

SELECT (2 * 365 * 24 * 3600) + (2 * 30 * 24 * 3600) + (1 * 24 * 3600) + (10 * 3600) + (5 * 60);

我遇到的问题是第一个查询 (SELECT extract...) 的结果生成了 68421900 而上面公式的查询生成了结果 68378700。
据我了解，结果应该是相同的，但结果之间存在 12 小时(43200 秒)的差异。为什么会这样？

重要的是要注意，如果我从间隔中删除年份，我会从两个查询中得到相同的结果，所以我猜这与年份有关。

Answer 1

你的问题是整数运算。错误在这里:

select extract ('epoch' from '1 year'::INTERVAL);

Produces the number 31557600. If we divide this number by (60*60*24) which is the number of seconds in a day, we get 365.

你可能做的是

SELECT 31557600 / (60 * 60 * 24);

 ?column? 
----------
      365
(1 row)

但现实是:

SELECT extract (epoch FROM INTERVAL '1 year') / (60 * 60 * 24);

 ?column? 
----------
   365.25
(1 row)

所以PostgreSQL一年的长度实际上是365天6小时。这是一个近似值(真实值略低)并且应该考虑闰年。

---

注意:这些值有点随意，因为“一个(日历)月有多长”或“一个(日历)年有多长”没有单一的正确答案——答案取决于具体的月份或年份.

如果您将 interval 添加到 timestamp with time zone，结果将始终正确，因为在该上下文中确切的长度是明确的。

---

关于为什么假定一年有 365.25 天，而一个月有 30 天的问题，这是源代码在 src/include/datatype/timestamp.h 中的说法:

/*
 * Assorted constants for datetime-related calculations
 */

#define DAYS_PER_YEAR   365.25  /* assumes leap year every four years */
#define MONTHS_PER_YEAR 12
/*
 *      DAYS_PER_MONTH is very imprecise.  The more accurate value is
 *      365.2425/12 = 30.436875, or '30 days 10:29:06'.  Right now we only
 *      return an integral number of days, but someday perhaps we should
 *      also return a 'time' value to be used as well.  ISO 8601 suggests
 *      30 days.
 */
#define DAYS_PER_MONTH  30              /* assumes exactly 30 days per month */
#define HOURS_PER_DAY   24              /* assume no daylight savings time changes */

/*
 *      This doesn't adjust for uneven daylight savings time intervals or leap
 *      seconds, and it crudely estimates leap years.  A more accurate value
 *      for days per years is 365.2422.
 */
#define SECS_PER_YEAR   (36525 * 864)   /* avoid floating-point computation */
#define SECS_PER_DAY    86400
#define SECS_PER_HOUR   3600
#define SECS_PER_MINUTE 60
#define MINS_PER_HOUR   60

所以我猜原因是 ISO 8601 规定一个月有 30 天。