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
但是我想获得下个月的定义,该怎么办?
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 days。我们期望1 month这两个值都指向每月的最后几天。如果将1天添加到值中,我们将获得下个月的第一天,
30 days
1 month
age('2012-12-01 00:00:00'::timestamp, '2012-11-01 00:00:00'::timestamp)
会给我们1个月的预期时间。因此,让我们检查是否有一个月的最后两天,在这种情况下,我们要返回第二天的年龄间隔。在其他情况下,我们将返回原始值的年龄间隔:
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)