时间戳

首页 > 解决方案 > 将 PostgreSQL 间隔转换为秒会产生错误的值

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

问题描述

我正在使用 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 秒）的差异。为什么会这样？

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

标签： postgresqlpostgresql-11

解决方案

你的问题是整数算术。错误在这里：

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 小时。这是一个近似值（真实值略小），应考虑闰年。

注意：这些值是任意的，因为对于“一个（日历）月有多长”或“一个（日历）年有多长”没有单一的正确答案——答案取决于各个月份或年份。

如果将 an 添加interval到 a 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 天。

推荐阅读