在PostgreSQL中计算两个时间戳之间的月数？

Question

我希望能够计算两个日期之间的月份数量。 进行如下操作：

SELECT TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP '2011-04-30 14:38:40';

返回值为： 0年0个月409天20小时0分钟0.00秒

因此：

SELECT extract(month from TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP '2011-04-30 14:38:40');

返回 0。

- GaetanZ

Answer 1

age 函数返回时间间隔：

age(timestamp1, timestamp2)

然后我们尝试从时间间隔中提取年份和月份，并相应地添加：

select extract(year from age(timestamp1, timestamp2)) * 12 +
extract(month from age(timestamp1, timestamp2))

- Angelin Nadar

---

好的解决方案，谢谢。如果时间差超过20天，我还添加了一个月给剩余天数： +case when extract(days from age(timestamp1, timestamp2)) > 20 then 1 else 0 end - The Coder

Answer 2

请注意，在尝试使用@ram和@angelin最受欢迎的答案计算日历月份差异时，该答案不准确。

select extract(year from age(timestamp1, timestamp2))*12 + extract(month from age(timestamp1, timestamp2))

例如，如果您尝试执行：

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))

结果将为0，但从二月到三月的月份差应该为1，不管日期之间的天数。

因此，公式应该如下所示，表示我们从时间戳1和时间戳2开始：

((year2-year1)*12) - month1 + month2 = 两个时间戳之间的日历月数

在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;

你可以创建一个如下的函数：

CREATE FUNCTION months_between (t_start timestamp, t_end timestamp)
RETURNS integer
AS $$
select ((extract('years' from $2)::int -  extract('years' from $1)::int) * 12) 
    - extract('month' from $1)::int + extract('month' from $2)::int
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

- Gabo

Answer 3

age函数提供一个合理的区间，以便进行处理：

SELECT age(TIMESTAMP '2012-06-13 10:38:40', TIMESTAMP '2011-04-30 14:38:40');

返回 1年1个月12天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);

- MatheusOl

---

聪明地添加年份和月份 :) - Andrew

Answer 4

如果您需要多次这样做，可以定义以下函数：

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($2 - $1))
        ) as a (i)
    $$
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

这样你就可以轻松地

SELECT months_between('2015-01-01', now());

- Marco

---

你可能需要将 now() 转换为时间戳。例如：select months_between('2015-01-01', now()::timestamp); - rbinnun

Answer 5

SELECT date_part ('year', f) * 12
      + date_part ('month', f)
FROM age ('2015-06-12', '2014-12-01') f

结果：6个月

- atiruz

Answer 6

这是一个与Oracle MONTHS_BETWEEN函数完全相同的PostgreSQL函数。它已经在广泛的年份（包括闰年）和超过700k个日期组合上进行了测试（包括每个月底）。

CREATE OR REPLACE FUNCTION months_between
(   DATE,
    DATE
)
RETURNS float
AS
$$
    SELECT
            (EXTRACT(YEAR FROM $1) - EXTRACT(YEAR FROM $2)) * 12
        +   EXTRACT(MONTH FROM $1) - EXTRACT(MONTH FROM $2)
        +   CASE
                WHEN    EXTRACT(DAY FROM $2) = EXTRACT(DAY FROM LAST_DAY($2))
                    AND EXTRACT(DAY FROM $1) = EXTRACT(DAY FROM LAST_DAY($1))
                THEN
                    0
                ELSE
                    (EXTRACT(DAY FROM $1) - EXTRACT(DAY FROM $2)) / 31
            END
    ;
$$
LANGUAGE SQL
IMMUTABLE STRICT;

这个函数需要一个LAST_DAY函数（与Oracle的函数行为相同）：

CREATE OR REPLACE FUNCTION last_day
(   DATE
)
RETURNS DATE
AS
$$
    SELECT
        (DATE_TRUNC('MONTH', $1) + INTERVAL '1 MONTH' - INTERVAL '1 DAY')::date
    ;
$$
LANGUAGE SQL
IMMUTABLE STRICT;

- Jean-François Wartelle

Answer 7

计算两个日期之间相差的月份数

   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' );

结果：14

需要分别提取两个日期的月份，然后计算它们之间的差异。

- Naveen Kumar Yadav

---

我认为我们正在朝着正确的方向前进，但仍然存在一些错误。使用您的建议得到的结果是14，而正确的答案是13。 - GaetanZ

选择 EXTRACT(year FROM age(TIMESTAMP '2012-06-13 10:38:40',TIMESTAMP '2011-04-30 14:38:40'))*12 + EXTRACT(month FROM age(TIMESTAMP '2012-06-13 10:38:40',TIMESTAMP '2011-04-30 14:38:40'));。 - GaetanZ

我已经编辑了你的答案并实现了解决方案。同行评审后应该可以看到它。 - GaetanZ

Answer 8

我曾经遇到过同样的问题，然后写了这个...它看起来相当丑陋：

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)

在这个解决方案中，“一个月”被定义为30.43天，因此在较短的时间跨度内可能会产生一些意外的结果。

- tobixen

---

它能工作，但很奇怪。我看到的另一个问题是你认为一个月是30天。从2月3日到3月4日有一个月。你的选择将返回0。而从1月3日到2月2日不是一个月。你的选择将返回1。 - GaetanZ

是的，不幸的是它只是大致正确的（但对我来说已经足够好了）。 - tobixen

由于月份的长短不同，因此使用“30”进行近似计算。时间戳之间的距离越大，这个问题就越突出。类似的时间戳舍入即可。 - pstanton

我已经将30替换为30.43，这样在长时间跨度上会更正确一些 :-) - tobixen

Answer 9

按年份和月份提取，将向下取整到月份：

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

这种方法保留了月份的分数部分（感谢 tobixen 提供的除数）。

select round(('2016-11-30'::date - '2015-10-15'::date)::numeric /30.43, 1); --> 13.5 months

- Rohanthewiz

Answer 10

我写了一个这样的函数：

/* similar to ORACLE's MONTHS_BETWEEN */
CREATE OR REPLACE FUNCTION ORACLE_MONTHS_BETWEEN(date_from DATE, date_to DATE)
RETURNS REAL LANGUAGE plpgsql
AS
$$
DECLARE age INTERVAL;
declare rtn real;
BEGIN
    age := age(date_from, date_to);
    rtn := date_part('year', age) * 12 + date_part('month', age) + date_part('day', age)/31::real;
    return rtn;
END;
$$;

Oracle 示例）

SELECT MONTHS_BETWEEN
(TO_DATE('2015-02-02','YYYY-MM-DD'), TO_DATE('2014-12-01','YYYY-MM-DD') ) 
"Months" FROM DUAL;
--result is: 2.03225806451612903225806451612903225806

我的 PostgreSQL 函数示例

select ORACLE_MONTHS_BETWEEN('2015-02-02'::date, '2014-12-01'::date) Months;
-- result is: 2.032258

从结果中，您可以使用CEIL（）/FLOOR（）进行四舍五入。

select ceil(2.032258)  --3
select floor(2.032258) --2

- monad.gon