PostgreSQL：两个日期之间的天/月/年

PostgreSQL: days/months/years between two dates

提问by gefei

I am looking for a way to implement the SQLServer-function datediffin PostgreSQL. That is,

我正在寻找一种在 PostgreSQL 中实现SQLServer 函数 datediff的方法。那是，

This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

此函数返回指定开始日期和结束日期之间跨越的指定日期部分边界的计数（作为有符号整数值）。

datediff(dd, '2010-04-01', '2012-03-05') = 704 // 704 changes of day in this interval
datediff(mm, '2010-04-01', '2012-03-05') = 23  // 23 changes of month
datediff(yy, '2010-04-01', '2012-03-05') = 2   // 2 changes of year

I know I could do 'dd' by simply using substraction, but any idea about the other two?

我知道我可以通过简单地使用减法来做“dd”，但是对另外两个有什么想法吗？

回答by mehdi

simply subtract them:

简单地减去它们：

select '2015-01-12'::date - '2015-01-01'::date;

This will give you:

这会给你：

?column? 
----------
   11

回答by Igor Romanchenko

Try something like:

尝试类似：

select age('2010-04-01', '2012-03-05'),
       date_part('year',age('2010-04-01', '2012-03-05')),
       date_part('month',age('2010-04-01', '2012-03-05')),
       date_part('day',age('2010-04-01', '2012-03-05'));

This functions will give you fullyears, month, days ... between two dates. For the number of date changes - us the link, provided by revoua : http://www.sqlines.com/postgresql/how-to/datediff

此函数将为您提供两个日期之间的完整年、月、日……。对于日期更改的数量 - 我们的链接，由 revoua 提供：http://www.sqlines.com/postgresql/how-to/datediff

回答by WebWanderer

I spent some time looking for the best answer, and I think I have it.

我花了一些时间寻找最佳答案，我想我已经找到了。

This sql will give you the number of days between two dates as integer:

此 sql 将为您提供两个日期之间的天数integer：

SELECT
    (EXTRACT(epoch from age('2017-6-15', now())) / 86400)::int

..which, when run today (2017-3-28), provides me with:

..它，当今天运行 ( 2017-3-28) 时，为我提供：

?column?
------------
77

The misconception about the accepted answer:

对已接受答案的误解：

select age('2010-04-01', '2012-03-05'),
   date_part('year',age('2010-04-01', '2012-03-05')),
   date_part('month',age('2010-04-01', '2012-03-05')),
   date_part('day',age('2010-04-01', '2012-03-05'));

..is that you will get the literal difference between the parts of the date strings, not the amount of time between the two dates.

.. 是您将获得日期字符串部分之间的字面差异，而不是两个日期之间的时间量。

I.E:

IE：

Age(interval)=-1 years -11 mons -4 days;

Age(interval)=-1 years -11 mons -4 days;

Years(double precision)=-1;

Years(double precision)=-1;

Months(double precision)=-11;

Months(double precision)=-11;

Days(double precision)=-4;

Days(double precision)=-4;

回答by Riki_tiki_tavi

Almost the same function as you needed (based on atiruz's answer, shortened version of UDF from here)

几乎与您需要的功能相同（基于 atiruz 的回答，这里是 UDF 的缩短版本）

CREATE OR REPLACE FUNCTION datediff(type VARCHAR, date_from DATE, date_to DATE) RETURNS INTEGER LANGUAGE plpgsql
AS
$$
DECLARE age INTERVAL;
BEGIN
    CASE type
        WHEN 'year' THEN
            RETURN date_part('year', date_to) - date_part('year', date_from);
        WHEN 'month' THEN
            age := age(date_to, date_from);
            RETURN date_part('year', age) * 12 + date_part('month', age);
        ELSE
            RETURN (date_to - date_from)::int;
    END CASE;
END;
$$;

Usage:

用法：

/* Get months count between two dates */
SELECT datediff('month', '2015-02-14'::date, '2016-01-03'::date);
/* Result: 10 */

/* Get years count between two dates */
SELECT datediff('year', '2015-02-14'::date, '2016-01-03'::date);
/* Result: 1 */

/* Get days count between two dates */
SELECT datediff('day', '2015-02-14'::date, '2016-01-03'::date);
/* Result: 323 */

/* Get months count between specified and current date */
SELECT datediff('month', '2015-02-14'::date, NOW()::date);
/* Result: 47 */

回答by atiruz

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

Result: 6

结果：6

回答by André C. Andersen

This question is full of misunderstandings. First lets understand the question fully. The asker wants to get the same result as for when running the MS SQL Server functionDATEDIFF ( datepart , startdate , enddate )where dateparttakes dd, mm, or yy.

这个问题充满了误解。首先让我们充分理解这个问题。提问者希望得到相同的结果运行时的MS SQL Server的功能DATEDIFF ( datepart , startdate , enddate )，其中datepart需要dd，mm或yy。

This function is defined by:

该函数定义为：

This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

此函数返回指定开始日期和结束日期之间跨越的指定日期部分边界的计数（作为有符号整数值）。

That means how many day boundaries, month boundaries, or year boundaries, are crossed. Not how many days, months, or years it is between them. That's why datediff(yy, '2010-04-01', '2012-03-05')is 2, and not 1. There is less than 2 years between those dates, meaning only 1 whole year has passed, but 2 year boundaries have crossed, from 2010 to 2011, and from 2011 to 2012.

这意味着跨越了多少天边界、月边界或年边界。不是他们之间有多少天、月或年。这就是为什么datediff(yy, '2010-04-01', '2012-03-05')是 2，而不是 1。这些日期之间的间隔不到 2 年，这意味着仅过去了整整 1 年，但跨越了 2 年的界限，从 2010 年到 2011 年，从 2011 年到 2012 年。

The following are my best attempt at replicating the logic correctly.

以下是我正确复制逻辑的最佳尝试。

-- datediff(dd`, '2010-04-01', '2012-03-05') = 704 // 704 changes of day in this interval
select ('2012-03-05'::date - '2010-04-01'::date );
-- 704 changes of day

-- datediff(mm, '2010-04-01', '2012-03-05') = 23  // 23 changes of month
select (date_part('year', '2012-03-05'::date) - date_part('year', '2010-04-01'::date)) * 12 + date_part('month', '2012-03-05'::date) - date_part('month', '2010-04-01'::date)
-- 23 changes of month

-- datediff(yy, '2010-04-01', '2012-03-05') = 2   // 2 changes of year
select date_part('year', '2012-03-05'::date) - date_part('year', '2010-04-01'::date);
-- 2 changes of year

回答by Daniel L. VanDenBosch

I would like to expand on Riki_tiki_tavi's answer and get the data out there. I have created a datediff function that does almost everything sql server does. So that way we can take into account any unit.

我想扩展 Riki_tiki_tavi 的答案并在那里获取数据。我创建了一个 datediff 函数，它几乎可以完成 sql server 所做的所有事情。这样我们就可以考虑任何单位。

create function datediff(units character varying, start_t timestamp without time zone, end_t timestamp without time zone) returns integer
language plpgsql
 as
 $$
DECLARE
 diff_interval INTERVAL; 
 diff INT = 0;
 years_diff INT = 0;
BEGIN
 IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN
   years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);

   IF units IN ('yy', 'yyyy', 'year') THEN
     -- SQL Server does not count full years passed (only difference between year parts)
     RETURN years_diff;
   ELSE
     -- If end month is less than start month it will subtracted
     RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t)); 
   END IF;
 END IF;

 -- Minus operator returns interval 'DDD days HH:MI:SS'  
 diff_interval = end_t - start_t;

 diff = diff + DATE_PART('day', diff_interval);

 IF units IN ('wk', 'ww', 'week') THEN
   diff = diff/7;
   RETURN diff;
 END IF;

 IF units IN ('dd', 'd', 'day') THEN
   RETURN diff;
 END IF;

 diff = diff * 24 + DATE_PART('hour', diff_interval); 

 IF units IN ('hh', 'hour') THEN
    RETURN diff;
 END IF;

 diff = diff * 60 + DATE_PART('minute', diff_interval);

 IF units IN ('mi', 'n', 'minute') THEN
    RETURN diff;
 END IF;

 diff = diff * 60 + DATE_PART('second', diff_interval);

 RETURN diff;
END;
$$;

回答by Vladimir Kunschikov

One more solution, version for the 'years' difference:

另一种解决方案，“年”差异的版本：

SELECT count(*) - 1 FROM (SELECT distinct(date_trunc('year', generate_series('2010-04-01'::timestamp, '2012-03-05', '1 week')))) x

---

---

    2

(1 row)

(1 行)

And the same trick for the months:

几个月来同样的把戏：

SELECT count(*) - 1 FROM (SELECT distinct(date_trunc('month', generate_series('2010-04-01'::timestamp, '2012-03-05', '1 week')))) x

---

---

   23

(1 row)

(1 行)

In real life query there can be some timestamp sequences grouped by hour/day/week/etc instead of generate_series.

在现实生活中的查询中，可以有一些按小时/天/周/等而不是 generate_series 分组的时间戳序列。

This 'count(distinct(date_trunc('month', ts)))'can be used right in the 'left' side of the select:

这'count(distinct(date_trunc('month', ts)))'可以在选择的“左侧”右侧使用：

SELECT sum(a - b)/count(distinct(date_trunc('month', c))) FROM d

I used generate_series() here just for the brevity.

我在这里使用 generate_series() 只是为了简洁。

回答by u5827450

Here is a complete example with output. psql (10.1, server 9.5.10).

这是一个带有输出的完整示例。psql（10.1，服务器 9.5.10）。

You get 58, not some value less than 30.
Remove age() function, solved the problem that previous post mentioned.

你得到 58，不是一些小于 30 的值。
删除 age() 函数，解决了上一篇文章提到的问题。

drop table t;
create table t(
    d1 date
);

insert into t values(current_date - interval '58 day');

select d1
, current_timestamp - d1::timestamp date_diff
, date_part('day', current_timestamp - d1::timestamp)
from t;

     d1     |        date_diff        | date_part
------------+-------------------------+-----------
 2018-05-21 | 58 days 21:41:07.992731 |        58