如何在PostgreSQL中获取两个日期之间的Months的数值? [英] How to get numeric value of Months between two dates in PostgreSQL?
问题描述
我有两个没有时区的时间戳格式的日期。
I have two dates in format Time Stamp Without Time Zone.
我想比较它们并获取它们之间月份的数值:
I want to compare them and get the numeric value of months between them:
select age(NOW(), '2012-03-24 14:44:55.454041+03')
送礼:
4 years 9 mons 2 days 21:00:27.165482
这里的窍门是我需要将此结果转换为一个值
The trick here is that I need to convert this result into one value of months.
因此:
为了将 YEARS
转换为月份
:
select EXTRACT(YEAR FROM age) * 12 + EXTRACT(MONTH FROM age)
FROM age(NOW(), '2012-06-24 14:44:55.454041+03') AS t(age)
我得到 57
,它是 4 * 12 + 9
。
我的问题是我不知道如何转换日期。
在上面的示例中,我需要将‘2天’转换为以月为单位的值。
2天
不是 0
个月!
My problem is that I don't know how to convert the days.
In the above example I need to convert the '2 days'
into it's value in months.
'2 days'
isn't 0
months!
在30个月中的15天是0.5个月。
In Months of 30 days 15 days are 0.5 months.
我该怎么做?
最终结果应为 57.something
推荐答案
您可以使用以下粗略估算:
You can get a rough estimation with:
select (extract(epoch from timestamptz '2012-06-24 14:44:55.454041+03')
- extract(epoch from timestamptz '2017-03-27 00:00:00+03'))
/ extract(epoch from interval '30.44 days') rough_estimation
(您可以用提取(从 1个月间隔开始的时间段)除法)
进行更粗略的估算。)
(you can divide with extract(epoch from interval '1 month')
for an even more rough estimation).
原始公式的问题在于,该公式旨在提供两个日期之间的完整月份差。如果您也要考虑天数,则会出现一个有趣的问题:在您的示例中,结果应为 57个月
和 2天21:00:27.165482
。但是应该在哪个月计算 2天21:00:27.165482
部分?平均长度的一个月( 30.44天
)?如果要精确起见,应该注意的是,在您的示例案例中,差异实际上只有 56个月
,再加上几乎 7天
2012-06
(其中有 30
天)和 27天的code>
在 2017-03
中(有 31
天)。您应该问自己的问题:是否真的值得一个先进的公式来考虑范围末端两个月的天数?
The problem with your original formula is that it is designed to give a complete month difference between two dates. If you want to account days too an interesting problem arises: in your example, the result should be 57 months
and 2 days 21:00:27.165482
. But in what month should the 2 days 21:00:27.165482
part is calculated? An average-length month (30.44 days
)? If you want to be precise, it should be noted that in your example case, the difference is really only 56 months
, plus almost 7 days
in 2012-06
(which had 30
days) and 27 days
in 2017-03
(which has 31
days). The question you should ask yourself: is it really worth an advanced formula which takes account both range ends' days-in-a-month or not?
编辑:为完整起见,以下是一个函数,可以同时考虑范围的两端:
Edit: For completeness, here is a function, which can take both range end into consideration:
create or replace function abs_month_diff(timestamptz, timestamptz)
returns numeric
language sql
stable
as $func$
select extract(year from age)::numeric * 12 + extract(month from age)::numeric
+ (extract(epoch from (lt + interval '1 month' - l))::numeric / extract(epoch from (lt + interval '1 month' - lt))::numeric)
+ (extract(epoch from (g - gt))::numeric / extract(epoch from (gt + interval '1 month' - gt))::numeric)
- case when gt <= l or lt = l then 1 else 0 end
from least($1, $2) l,
greatest($1, $2) g,
date_trunc('month', l) lt,
date_trunc('month', g) gt,
age(gt, l)
$func$;
(注意:如果您使用 timestamp
的 timestamptz
,此函数是不可变的
,而不是稳定的
。就像 date_trunc
函数一样。)
(Note: if you use timestamp
instead of timestamptz
, this function is immutable
instead of stable
. Just like the date_trunc
functions.)
所以:
select age('2017-03-27 00:00:00+03', '2012-06-24 14:44:55.454041+03'),
abs_month_diff('2017-03-27 00:00:00+03', '2012-06-24 14:44:55.454041+03');
将产生:
age | abs_month_diff
---------------------------------------+-------------------------
4 years 9 mons 2 days 09:15:04.545959 | 57.05138456751051843959
http://rextester.com/QLABV31257 (已过时)
编辑:在以下情况下,该函数经过更正以产生准确的结果差异少于一个月。
Edit: function is corrected to produce exact results when the difference is less than a month.
请参见f.ex:
set time zone 'utc';
select abs_month_diff('2017-02-27 00:00:00+03', '2017-02-24 00:00:00+03'), 3.0 / 28,
abs_month_diff('2017-03-27 00:00:00+03', '2017-03-24 00:00:00+03'), 3.0 / 31,
abs_month_diff('2017-04-27 00:00:00+03', '2017-04-24 00:00:00+03'), 3.0 / 30,
abs_month_diff('2017-02-27 00:00:00+00', '2017-03-27 00:00:00+00'), 2.0 / 28 + 26.0 / 31;
http://rextester.com/TIYQC5325 (已过时)
编辑2 :此函数基于以下公式,以计算一个月的长度:
Edit 2: This function is based on the following formula, to calculate the length of a month:
select (mon + interval '1 month' - mon)
from date_trunc('month', now()) mon
这甚至会考虑DST更改。 F.ex.在我的国家/地区,昨天(在 2017-03-26
)的夏令时更改了,所以今天( 2017-03-27
)以上查询报告: 30天23:00:00
。
This will even take DST changes into account. F.ex. in my country there was a DST change yesterday (on 2017-03-26
), so today (2017-03-27
) the above query reports: 30 days 23:00:00
.
编辑3 :功能再次得到纠正(感谢@Jonathan,他注意到边缘盒的边缘盒)。
Edit 3: Function is corrected again (thanks to @Jonathan who noticed an edge-case of an edge-case).
这篇关于如何在PostgreSQL中获取两个日期之间的Months的数值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!