将 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);

生成数字31557600。如果我们把这个数字除以(60*60*24)，这是一天中的秒数，我们得到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天。

Answer 2

我遇到了类似的问题，最终找到了有效的方法。

SELECT AVG( EXTRACT(EPOCH FROM (end_ts - start_ts)) )

生成不正确的值。

SELECT AVG( EXTRACT(EPOCH FROM INTERVAL (end_ts - start_ts)) )

产生语法错误。

从开始和停止时间戳中分别提取纪元，然后对它们运行数学运算是有效的。

SELECT AVG( ( EXTRACT(EPOCH FROM end_ts) - EXTRACT(EPOCH FROM start_ts) ) )

我投入了一些额外的数学来翻译成小时。

SELECT AVG( ( EXTRACT(EPOCH FROM end_ts) - EXTRACT(EPOCH FROM start_ts) )::FLOAT/86400 )

Answer 3

首先，你So a month in postgresql interval has 30 days.陈述是错误的：

janbet=> select '2019-01-01'::date + '1 month'::interval;
?column?
---------------------
2019-02-01 00:00:00
(1 row)
janbet=> select '2019-02-01'::date + '1 month'::interval;
?column?
---------------------
2019-03-01 00:00:00
(1 row)

如上所示，月份可能是 31 天或 28 天，具体取决于上下文。事实上，一个月和月份有不同的长度。

这同样适用于'2 year 2 month 1 day 10 hour 5 minute'::interval：

janbet=> select '2019-01-01'::date + '2 year 2 month 1 day 10 hour 5 minute'::interval - '2019-01-01'::date;
?column?
-------------------
791 days 10:05:00
(1 row)
janbet=> select '2021-01-01'::date + '2 year 2 month 1 day 10 hour 5 minute'::interval - '2021-01-01'::date;
?column?
-------------------
790 days 10:05:00
(1 row)

我们得到不同的结果，具体取决于这些2 years是否包括闰年。

我对你的 12 小时的最佳猜测是，2 年包括 50% 的情况下的闰年，所以这是one day * 1/2.事实上，如果你用"3 年"重复计算，你会得到 18 小时的差异，所以我确信它是这样工作的。

我知道这可能会令人惊讶，但我想不出其他结果与年/月的可变长度一致，这是理想的interval行为。