Date Subtraction in Postgres and include both the dates-CodePudding

I am trying to get the difference between two dates using this

'2021-07-02'::date - '2020-07-03'::date

The actual difference if you include the end date as well is 365 days but the result from this query is 364 days. Has anyone worked with something similar?

I did think about adding 1 to the result but just wanted to check if there is a more elegant way to do this.

CodePudding user response：

That is documented behavior per Date/time operators. The reason being that the date subtraction is from Midnight to Midnight so you don't get the first day included. As demo:

--The date is effectively set to Midnight
select '2020-07-03'::date::timestamp;
 timestamp      
---------------------
 2020-07-03 00:00:00
--Which means this
select '2021-07-02'::date - '2020-07-03'::date;
 ?column? 
----------
      364
--Is the same as
select '2021-07-02 00:00:00'::timestamp - '2020-07-03 00:00:00'::timestamp;
 ?column? 
----------
 364 days

If you want to include the start date then you need to go to the previous days Midnight:

select '2020-07-03 00:00:00'::timestamp - '1 day'::interval;
      ?column?       
---------------------
 2020-07-02 00:00:00

select '2021-07-02 00:00:00'::timestamp - ('2020-07-03 00:00:00'::timestamp - '1 day'::interval);
 ?column? 
----------
 365 days

--Getting back to date.
select '2021-07-02'::date - ('2020-07-03 00:00:00'::date - '1 day'::interval)::date;
 ?column? 
----------
      365

Or an alternate solution:

select 
    count(*) 
from 
    generate_series('2020-07-03'::timestamp, '2021-07-02'::timestamp, '1 day'); 
365

This generates a set of dates at one day intervals over the time period and the count(*) adds them up.