PostgreSQL days months years 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 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

User · Answer

SELECT date part   year   f    12        date part   month   f  FROM age   2015-06-12   DATE   2014-12-01   DATE  f   Result  6

User · Answer

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

User · Answer

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

User · Answer

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 quot  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 -  lt your date gt   timestamp     86400

User · Answer

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

User · Answer

Simply subtract them   SELECT   2015-01-12   date -  2015-01-01   date  AS days    The result    days ------    11

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] PostgreSQL: days/months/years between two dates

Examples related to postgresql

Examples related to date