我有两个没有时区的时间戳格式的日期。
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.05138456751051843959rextester/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;rextester/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).
rextester/JLG68351
更多推荐
如何在PostgreSQL中获取两个日期之间的Months的数值?
发布评论