137

I am looking for a way to implement the SQLServer-function datediff in PostgreSQL. That is, this function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified start date and end date.

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 subtraction, but any idea about the other two?

ivanleoncz
  • 9,070
  • 7
  • 57
  • 49
gefei
  • 18,922
  • 9
  • 50
  • 67

10 Answers10

225

Simply subtract them:

SELECT ('2015-01-12'::date - '2015-01-01'::date) AS days;

The result:

 days
------
   11
Paweł Gościcki
  • 9,066
  • 5
  • 70
  • 81
mehdi
  • 2,703
  • 1
  • 13
  • 15
  • 5
    Yes, this works for PostgreSQL when you need to know number of days between two dates. – icl7126 Oct 06 '15 at 14:08
  • Great! FYI I used it to order records by smaller range between two dates, ex.: `range_end - range_start ASC, id DESC` – Edison Machado Oct 22 '15 at 20:50
  • 1
    Be aware, this method returns type `interval`, which cannot simply be cast as `int`. [How do I convert an interval into a number of hours with postgres?](http://stackoverflow.com/questions/952493/how-do-i-convert-an-interval-into-a-number-of-hours-with-postgres). Using the `date_part` / `age` function combo, like mentioned in @IgorRomanchenko 's answer will return type `double precision` – WebWanderer Mar 28 '17 at 18:14
  • 3
    On second thought, this is the correct way. Using this method to get the number of days between two dates will count days between months and years, while the `date_part` / `age` answer, as accepted, will provide days as the difference in the days part of the two dates. `date_part('day', age('2016-9-05', '2015-10-02'))` returns 3. – WebWanderer Mar 28 '17 at 22:33
  • 3
    The question was not about days, it is about the number of month and year boundaries that have to been crossed between two dates. The asker already knew how to do days. – André C. Andersen Jan 16 '19 at 01:13
164
SELECT
  AGE('2012-03-05', '2010-04-01'),
  DATE_PART('year', AGE('2012-03-05', '2010-04-01')) AS years,
  DATE_PART('month', AGE('2012-03-05', '2010-04-01')) AS months,
  DATE_PART('day', AGE('2012-03-05', '2010-04-01')) AS days;

This will give you full years, month, days ... between two dates:

          age          | years | months | days
-----------------------+-------+--------+------
 1 year 11 mons 4 days |     1 |     11 |    4

More detailed datediff information.

Paweł Gościcki
  • 9,066
  • 5
  • 70
  • 81
Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
  • 8
    +1 because of the age function, but i think it's arguments are in the wrong order :) – dcarneiro Oct 29 '13 at 18:51
  • 3
    `select date_part('month', age('2010-04-01', '2012-03-05'));` gives `-11`. That's not a correct difference in months – smac89 Nov 17 '15 at 01:27
  • 1
    select date_part('month', age('2012-03-05', '2010-04-01')); – Zuko Feb 26 '16 at 05:57
  • 48
    this doesn't account for months + years etc. It just does a rolling day check - i.e. `SELECT date_part('day', age('2016-10-05', '2015-10-02'))` returns `3` – Don Cheadle Oct 05 '16 at 19:13
  • 1
    The question is how to count how many year boundary crossings, and how many month boundary crossings there are between two dates. Using `age()` will always be wrong to do this for years and months. Between `2014-12-31` and `2015-01-01` ago will give 0 for month and year, while `datediff()` will give 1 and 1. You can't just add one to the `age()` result because `age()+1` will give 1 when between `2015-01-01` and `2015-01-02`, whereas `datediff()` would now gives 0. – André C. Andersen Jan 16 '19 at 01:18
  • 2
    This is incorrect. `select date_part('month',age('2010-04-01', '2012-03-05'))` returns -11 and not OP's requested 23. You're ignoring the years. – Cerin Mar 24 '20 at 23:01
  • 2
    This should absolutely not be the answer. Clearly this was just accepted without even bothering to test - as several other comments have noted, none of the tests actually work. Don't use this. – bsplosion Dec 09 '22 at 04:41
63

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:

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

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

?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:

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

Years(double precision)=-1;

Months(double precision)=-11;

Days(double precision)=-4;

WebWanderer
  • 10,380
  • 3
  • 32
  • 51
  • 9
    This should be the accepted answer. The only tweak I suggest is to use ceil() instead of casting to int (to round up partial days, instead of truncating). – Rob May 09 '17 at 14:49
  • 2
    Good point @Rob . Readers should make note of this. I can see this as being more of a preference. I think that in most of my cases, I would want to truncate my value as the literal number of days between dates, but I can see where rounding the number of days should be used as well. – WebWanderer May 09 '17 at 17:05
  • 3
    This approach can be tripped up by UK daylight savings time - there'll be one day a year with only 23 hours, and another with 25. – qcode peter Aug 16 '17 at 14:59
  • Wow @qcodepeter ! Good catch! You are absolutely correct! How do we fix that? – WebWanderer Aug 16 '17 at 16:15
  • 1
    This does not really answer the question. The question is how to count how many year boundary crossings, and how many month boundary crossings there are between two dates. This answer only answers how many days there are, and doesn't account for leap years. – André C. Andersen Jan 16 '19 at 01:11
  • Using the age() function might result in inaccurate results. e.g. days between '2019-07-29' and '2020-06-25' should return 332, however, your function returns 327. Because the age() returns '10 mons 27 days" and it treats each month as 30 days which is incorrect. You shold use the timestamp to get the result e.g. ceil((select extract(epoch from (current_date::timestamp - ::timestamp)) / 86400)). – Shengfeng Li Jun 25 '20 at 20:02
  • This is the correct answer. @AndréC.Andersen the different boundary crossings can be achieved by changing the division for average years (`31536000`), months (`2628000`). Agreed, the average calculation is out of context, however; an interval itself is also out of context once it is produced. – Brett Ryan Sep 10 '20 at 02:47
  • @BrettRyan I don't remember the details about this question, but as I remember it the question is about replicating a specific function from SQL Server into PostgreSQL. This answer does not do that from what I recall. See my answer for details: https://stackoverflow.com/a/54209000/604048 – André C. Andersen Sep 16 '20 at 08:11
  • 2
    This comment took into consideration relative dating in postgres--I kept coming up with 0 days for today and yesterday because its computing as a FULL day versus hours divided by 24.00; (EXTRACT(epoch from age(date_id::timestamp at time zone 'America/Los_Angeles', current_timestamp at time zone 'America/Los_Angeles') / 86400)::int ) – Tek Mailer Nov 14 '21 at 19:48
14

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

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 */
Riki_tiki_tavi
  • 827
  • 8
  • 15
  • This answer is incorrect. The `DATEDIFF()` function in MS SQL returns `1` for `datediff(year, '2015-02-14', '2016-01-03')`. This is because you have to pass the year boundary once between those dates: https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017 – André C. Andersen Jan 16 '19 at 01:04
  • 2
    The answer is correct because the original question was about PostgreSQL, not MS SQL. – Riki_tiki_tavi Jan 18 '19 at 07:19
  • I understand it can be hard pick up on, but the original question was to find "a way to implement the SQLServer-function datediff in PostgreSQL". Users of MS SQL Server tend to just call it SQL Server. Try googling "SQL Server": https://i.imgur.com/USCHdLS.png The user wanted to port a function from one specific technology to antoher. – André C. Andersen Jan 20 '19 at 16:12
  • Sorry, you were right. It seems that I was hurrying and didn't understand the meaning of your first comment. The answer was updated. – Riki_tiki_tavi Jan 21 '19 at 05:37
7
SELECT date_part ('year', f) * 12
     + date_part ('month', f)
FROM age ('2015-06-12'::DATE, '2014-12-01'::DATE) f

Result: 6

atiruz
  • 2,782
  • 27
  • 36
5

@WebWanderer 's answer is very close to the DateDiff using SQL server, but inaccurate. That is because of the usage of age() function.

e.g. days between '2019-07-29' and '2020-06-25' should return 332, however, using the age() function it will returns 327. Because the age() returns '10 mons 27 days" and it treats each month as 30 days which is incorrect.

You shold use the timestamp to get the accurate result. e.g.

ceil((select extract(epoch from (current_date::timestamp - <your_date>::timestamp)) / 86400))

Shengfeng Li
  • 606
  • 7
  • 11
2

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 function DATEDIFF ( datepart , startdate , enddate ) where datepart takes dd, mm, or 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.

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
André C. Andersen
  • 8,955
  • 3
  • 53
  • 79
1

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.

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;
$$;
Daniel L. VanDenBosch
  • 2,350
  • 4
  • 35
  • 60
0

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

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

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
Charlie 木匠
  • 2,234
  • 19
  • 19
0

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)

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)

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

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

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

I used generate_series() here just for the brevity.

Linuxmint
  • 4,716
  • 11
  • 44
  • 64
Vladimir Kunschikov
  • 1,735
  • 1
  • 16
  • 16