How to get difference of days/months/years (datediff) between two dates?

Question

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?

Squeaky

Answer 1

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.

Reconnoitre

+1 because of the age function, but i think it's arguments are in the wrong order :) – Otha

select date_part('month', age('2010-04-01', '2012-03-05')); gives -11. That's not a correct difference in months – Dunlop

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

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 – Compartmentalize

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. – Aerodonetics

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. – Trueblood

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. – Darill

Answer 2

Simply subtract them:

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

The result:

 days
------
   11

Calamint

Yes, this works for PostgreSQL when you need to know number of days between two dates. – Actinopod

Great! FYI I used it to order records by smaller range between two dates, ex.: range_end - range_start ASC, id DESC – Nave

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?. Using the date_part / age function combo, like mentioned in @IgorRomanchenko 's answer will return type double precision – Aerophyte

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. – Aerophyte

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. – Aerodonetics

Subtracting date columns, is the way to go, similar to this example. EXTRACT(DAYS FROM col) on calculations, then filtering results, might present the wrong result set, and you might get crazy (word). – Assortment

Answer 3

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;

Aerophyte

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). – Nelle

Good point @Nelle . 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. – Aerophyte

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. – Rarely

Wow @qcodepeter ! Good catch! You are absolutely correct! How do we fix that? – Aerophyte

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. – Aerodonetics

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 - <your_date>::timestamp)) / 86400)). – Eucaine

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. – Cauca

@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://mcmap.net/q/158632/-how-to-get-difference-of-days-months-years-datediff-between-two-dates – Aerodonetics

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

Answer 4

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 */

Moazami

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: learn.microsoft.com/en-us/sql/t-sql/functions/… – Aerodonetics

The answer is correct because the original question was about PostgreSQL, not MS SQL. – Moazami

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": i.imgur.com/USCHdLS.png The user wanted to port a function from one specific technology to antoher. – Aerodonetics

Sorry, you were right. It seems that I was hurrying and didn't understand the meaning of your first comment. The answer was updated. – Moazami

Answer 5

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

Result: 6

Bink

Answer 6

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

Eucaine

Answer 7

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

Jerusalem

Answer 8

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

Ikhnaton

Answer 9

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

Centavo

This answer does not answer the question of how to replicate datediff(yy, '2010-04-01', '2012-03-05') = 2, and month version. – Aerodonetics

Answer 10

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.

Savino