找出两个日期之间的月数 [英] Find out number of months between 2 dates

问题描述

select 
  (age('2012-11-30 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp)),
  (age('2012-12-31 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp)),
  (age('2013-01-31 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp)),
  (age('2013-02-28 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp))

给出以下内容:

  0 years 0 mons 30 days 0 hours 0 mins 0.00 secs
  0 years 2 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 3 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 3 mons 28 days 0 hours 0 mins 0.00 secs

但是我想要下个月的定义，我该怎么做?

But I want to have the following month definition , how can I do it?

  0 years 1 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 2 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 3 mons 0 days 0 hours 0 mins 0.00 secs
  0 years 4 mons 0 days 0 hours 0 mins 0.00 secs

推荐答案

表达式

age('2012-11-30 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp) 

给 30 天.我们期待 1 个月，因为这两个值都指向一个月的最后几天.如果我们将 1 天添加到值中，我们将得到下个月的第一天和

gives 30 days. We are expecting 1 month as both values point to last days of month. If we add 1 day to the values we shall get first days of next month and

age('2012-12-01 00:00:00'::timestamp, '2012-11-01 00:00:00'::timestamp)

将按预期给我们 1 个月的时间.所以让我们检查一下我们是否有一个月的最后两天，在这种情况下返回接下来几天的年龄间隔.在其他情况下，我们将返回原始值的年龄间隔:

will give us 1 month as expected. So let us check if we have two last days of month and in this case return age interval of the next days. In other cases we shall return age interval of original values:

create or replace function age_m (t1 timestamp, t2 timestamp)
returns interval language plpgsql immutable
as $$
declare
    _t1 timestamp = t1+ interval '1 day';
    _t2 timestamp = t2+ interval '1 day';
begin
    if extract(day from _t1) = 1 and extract(day from _t2) = 1 then
        return age(_t1, _t2);
    else
        return age(t1, t2);
    end if;
end $$;

一些例子:

with my_table(date1, date2) as (
values
    ('2012-11-30 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp),
    ('2012-12-31 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp),
    ('2013-01-31 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp),
    ('2013-02-28 00:00:00'::timestamp, '2012-10-31 00:00:00'::timestamp)
)

select *, age(date1, date2), age_m(date1, date2)
from my_table

        date1        |        date2        |      age       | age_m  
---------------------+---------------------+----------------+--------
 2012-11-30 00:00:00 | 2012-10-31 00:00:00 | 30 days        | 1 mon
 2012-12-31 00:00:00 | 2012-10-31 00:00:00 | 2 mons         | 2 mons
 2013-01-31 00:00:00 | 2012-10-31 00:00:00 | 3 mons         | 3 mons
 2013-02-28 00:00:00 | 2012-10-31 00:00:00 | 3 mons 28 days | 4 mons
(4 rows)

这篇关于找出两个日期之间的月数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！