postgresql 计算postgresql上两个时间戳之间的月份数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11012629/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count months between two timestamp on postgresql?
提问by GaetanZ
I want to count the number of months between two dates.
我想计算两个日期之间的月数。
Doing :
正在做 ：
SELECT TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP '2011-04-30 14:38:40';
Returns : 0 years 0 mons 409 days 20 hours 0 mins 0.00 secs
回报：0 年 0 月 409 天 20 小时 0 分 0.00 秒
and so:
所以：
SELECT extract(month from TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP '2011-04-30 14:38:40');
returns 0.
返回 0。
回答by Angelin Nadar
agefunction returns interval:
age函数返回区间：
age(timestamp1, timestamp2)
Then we try to extract year and month out of the interval and add them accordingly:
然后我们尝试从间隔中提取年和月并相应地添加它们：
select extract(year from age(timestamp1, timestamp2)) * 12 +
extract(month from age(timestamp1, timestamp2))
回答by MatheusOl
The agefunction give a justified interval to work with:
该age函数给出了一个合理的间隔：
SELECT age(TIMESTAMP '2012-06-13 10:38:40', TIMESTAMP '2011-04-30 14:38:40');
returns 1 year 1 mon 12 days 20:00:00, and with that you can easily use EXTRACTto count the number of months:
Returns 1 year 1 mon 12 days 20:00:00，并且您可以轻松地使用它EXTRACT来计算月数：
SELECT EXTRACT(YEAR FROM age) * 12 + EXTRACT(MONTH FROM age) AS months_between
FROM age(TIMESTAMP '2012-06-13 10:38:40', TIMESTAMP '2011-04-30 14:38:40') AS t(age);
回答by Marco
If you will do this multiple times, you could define the following function:
如果您将多次执行此操作，则可以定义以下函数：
CREATE FUNCTION months_between (t_start timestamp, t_end timestamp)
RETURNS integer
AS $$
SELECT
(
12 * extract('years' from a.i) + extract('months' from a.i)
)::integer
from (
values (justify_interval( - ))
) as a (i)
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
so that you can then just
这样你就可以
SELECT months_between('2015-01-01', now());
回答by Gabo
Please note that the most voted answer by @ramand @angelinis not accurate when you are trying to get calendar month difference using.
请注意，当您尝试使用日历月差时，@ram和@angelin投票最多的答案是不准确的。
select extract(year from age(timestamp1, timestamp2))*12 + extract(month from age(timestamp1, timestamp2))
for example, if you try to do:
例如，如果您尝试执行以下操作：
select extract(year from age('2018-02-02'::date, '2018-03-01'::date))*12 + extract(month from age('2018-02-02'::date , '2018-03-01'::date))
the result will be 0but in terms of months between March from February should be 1 no matter the days between dates.
结果将为 0，但无论日期之间的天数如何，从 3 月到 2 月之间的月份都应为 1。
so the formula should be like the following saying that we start with timestamp1 and timestamp2:
所以公式应该如下所示，我们从timestamp1和timestamp2开始：
((year2 - year1)*12) - month1 + month2 = calendar months between two timestamps
((year2 - year1)*12) - month1 + month2 = 两个时间戳之间的日历月
in pg that would be translated to:
在 pg 中将被翻译为：
select ((extract('years' from '2018-03-01 00:00:00'::timestamp)::int - extract('years' from '2018-02-02 00:00:00'::timestamp)::int) * 12)
- extract('month' from '2018-02-02 00:00:00'::timestamp)::int + extract('month' from '2018-03-01 00:00:00'::timestamp)::int;
you can create a function like:
您可以创建一个函数，如：
CREATE FUNCTION months_between (t_start timestamp, t_end timestamp)
RETURNS integer
AS $$
select ((extract('years' from )::int - extract('years' from )::int) * 12)
- extract('month' from )::int + extract('month' from )::int
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
回答by atiruz
SELECT date_part ('year', f) * 12
+ date_part ('month', f)
FROM age ('2015-06-12', '2014-12-01') f
Result: 6 Months
结果：6个月
回答by Naveen Kumar Yadav
Gives the differenece of months of two dates
给出两个日期的月份差异
SELECT ((extract( year FROM TIMESTAMP '2012-06-13 10:38:40' ) - extract( year FROM TIMESTAMP '2011-04-30 14:38:40' )) *12) + extract(MONTH FROM TIMESTAMP '2012-06-13 10:38:40' ) - extract(MONTH FROM TIMESTAMP '2011-04-30 14:38:40' );
The Result : 14
结果：14
Have to extract months seperately for both the dates and then the difference of both the results
必须分别提取两个日期的月份，然后提取两个结果的差异
回答by tobixen
I had the same problem once upon a time and wrote this ... it's quite ugly:
我曾经遇到过同样的问题并写了这个......它很丑陋：
postgres=> SELECT floor((extract(EPOCH FROM TIMESTAMP '2012-06-13 10:38:40' ) - extract(EPOCH FROM TIMESTAMP '2005-04-30 14:38:40' ))/30.43/24/3600);
floor
-------
85
(1 row)
In this solution "one month" is defined to be 30.43 days long, so it may give some unexpected results over shorter timespans.
在此解决方案中，“一个月”被定义为 30.43 天，因此它可能会在较短的时间跨度内产生一些意想不到的结果。
回答by Laci
Try this solution:
试试这个解决方案：
SELECT extract (MONTH FROM age('2014-03-03 00:00:00'::timestamp,
'2013-02-03 00:00:00'::timestamp)) + 12 * extract (YEAR FROM age('2014-03-03
00:00:00'::timestamp, '2013-02-03 00:00:00'::timestamp)) as age_in_month;
回答by Rohanthewiz
Extract by year and months will floor on months:
按年份和月份提取将按月份计算：
select extract(year from age('2016-11-30'::timestamp, '2015-10-15'::timestamp)); --> 1
select extract(month from age('2016-11-30'::timestamp, '2015-10-15'::timestamp)); --> 1
--> Total 13 months
This approach maintains fractions of months (thanks to tobixen for the divisor)
这种方法保持几个月的分数（感谢 tobixen 的除数）
select round(('2016-11-30'::date - '2015-10-15'::date)::numeric /30.43, 1); --> 13.5 months
回答by amit
SELECT floor(extract(days from TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP
'2011-04-30 14:38:40')/30.43)::integer as months;
Gives an approximate value but avoids duplication of timestamps. This uses hint from tobixen's answerto divide by 30.43 in place of 30 to be less incorrect for long timespans while computing months.
给出一个近似值，但避免时间戳重复。这使用来自tobixen 的答案的提示，除以 30.43 代替 30，以便在计算月份时在长时间跨度中不那么不正确。
