问题描述
我有两个没有时区的时间戳格式的日期。
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天（其中有 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
（已过时）
编辑：在以下情况下，该函数经过更正以产生准确的结果差异少于一个月。
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;
（已过时）
编辑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的数值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！