PostgreSQL age（）函数：在不同月份登陆时的结果不同/意外 [英] PostgreSQL age() function: different/unexpected results when landing in dfferent month

问题描述

今天，运行此查询时，我在PostgreSQL 9.6中遇到无法解释的结果：

Today, I've encountered an unexplainable result in PostgreSQL 9.6 while running this query:

SELECT age('2018-06-30','2018-05-19') AS one,
       age('2018-07-01','2018-05-20') AS two; 

两列的预期结果： 1周一11天。但是，仅在2018年5月19日至2018年6月30日之间，我得到了我所期望的，而从2018年5月20日到2018年7月1日，我将获得更多的一天： 1周一12天

Expected results for both columns: 1 mon 11 days. However, only for the interval from 2018-05-19 to 2018-06-30, I get what I expect, while for 2018-05-20 till 2018-07-01 I'll get one day more: 1 mon 12 days

我不知道为什么会这样，据我所知，在2018-05-20之间-07-01只是 1个星期一11天的间隔，这里的Postgres结果是错误的。

I don't get why this is the case and in my understanding, between 2018-05-20 2018-07-01 is just an interval of 1 mon 11 days and the Postgres result here is wrong.

I无法找到有关PostgreSQL- age（timestamp，timestamp）函数的工作原理的任何深入信息。但是，我假设该函数的作用类似于：从月的开始日期向前走，直到达到月结束。从那里转到结束日期。总结了几个月和几天。

I cannot find any in-depth information on how exactly the PostgreSQL-age(timestamp,timestamp) function works. However, I assumed that function does something like: Go from the start date in month steps forward till you reach the end month. From there, go to the day of the end date. Sum up months and days.

所以，根据我的理解，这就是我的情况（抱歉，在这里太冗长了，但是我觉得有必要）

So, in my understanding, this is what should go on under the hood in my case (sorry, for being so verbose here, but I feel it's necessary):

从2018-05-19开始。向前走一个月。于2018-06-19着陆。往前走 N 天，直到到达2018-06-30：

Start at 2018-05-19. Go one month forward. Land at 2018-06-19. Walk N days forward till you've reached 2018-06-30:

1 day: 20
2 days: 21
3 days: 22
4 days: 23
5 days: 24
6 days: 25
7 days: 26
8 days: 27
9 days: 28
10 days: 29
11 days: 30

= 1 month 11 days.

在2018-05-20和2018-07-01之间的时间应该几乎相同：

For the time between 2018-05-20 and 2018-07-01 it should be almost the same:

从2018-05-20开始。向前走一个月。于2018-06-20着陆。向前走 N 天，直到到达2018-07-01：

Start at 2018-05-20. Go one month forward. Land at 2018-06-20. Walk N days forward till you've reached 2018-07-01:

1 day: 21
2 days: 22
3 days: 23
4 days: 24
5 days: 25
6 days: 26
7 days: 27
8 days: 28
9 days: 29
10 days: 30
11 days: 1

= 1 month 11 days.

这是我的错误还是PostgreSQL之一？
是否有其他功能/算法可以按照我描述/期望的方式工作？

Is this my mistake or one of PostgreSQL? Are there alternative functions/algorithms which work the way I described/expect?

推荐答案

年龄由 src / backend / utils / adt / timestamp.c timestamptz_age 函数计算c>。注释说：

age is calculated by the timestamptz_age function in src/backend/utils/adt/timestamp.c. The comment says:

/* timestamptz_age()
 * Calculate time difference while retaining year/month fields.
 * Note that this does not result in an accurate absolute time span
 *  since year and month are out of context once the arithmetic
 *  is done.
 */

代码首先将参数转换为 struct pg_tm 变量 tm1 和 tm2 （ struct pg_tm 与C库的 struct tm 类似，但具有其他时区字段），然后计算差值 tm 每个字段。

The code first converts the arguments to struct pg_tm variables tm1 and tm2 (struct pg_tm is similar to the C library's struct tm, but has additional time zone fields) and then calculates the difference tm per field.

对于 age（'2018-07-01'，'2018-05-20'），则该差异的相关字段如下：

In the case of age('2018-07-01','2018-05-20'), the relevant fields of that difference would look like this:

tm_mday = -19
tm_mon  =   2
tm_year =   0

现在调整负字段。对于 tm_mday ，代码看起来像这样：

Now negative fields are adjusted. for tm_mday, the code looks like this:

while (tm->tm_mday < 0)
{
    if (dt1 < dt2)
    {
        tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
        tm->tm_mon--;
    }
    else
    {
        tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
        tm->tm_mon--;
    }
}

由于 dt1> dt2 ，则使用 else 分支，并且该代码添加了5月的天数（31），并将月份减少了1，最后与

Since dt1 > dt2, the else branch is taken, and the code adds the number of days in May (31) and reduces the month by 1, ending up with

tm_mday = 12
tm_mon  =  1
tm_year =  0

这就是您得到的结果。

现在乍看之下 tm2-> tm_mon 不是合适的月份，最好选择左参数的前一个月份：

Now at first glance it seems that tm2->tm_mon isn't the right month to choose, and it would have been better to take the previous month of the left argument:

day_tab[isleap(tm1->tm_year)][(tm1->tm_mon + 10) % 12]

但是我不能说这种选择在所有情况下是否都更好，无论如何注释都会损害该功能，因此我不愿称它为错误。

But I cannot say if that choice would be better in all cases, and in any event the comment indemnifies the function, so I'd hesitate to call it a bug.

您可能想利用黑客邮件列表来解决它。

You might want to take it up with the hackers mailing list.

这篇关于PostgreSQL age（）函数：在不同月份登陆时的结果不同/意外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！