Calculating the number of days between two dates in PostgreSQL

Question

I am using the PostgreSQL function date_part() to calculate the (inclusive) number of days passed between two dates.

select date_part('day', '2019-12-31 00:00:00'::timestamp
                      - '2019-01-01 00:00:00'::timestamp);

Running this query in DBeaver returns 1 day less than expected:

date_part
364.0

It would be as simple as adding +1 to the result, but then the following sentence would return 1 day, which seems wrong:

select 1 + date_part('day', '2019-01-01 00:00:00'::timestamp
                          - '2019-01-01 00:00:00'::timestamp);

?column?
1.0

Is there a better way to have a trusted solution?

Answer 1

If you want to include both date boundaries, adding + 1 is actually the right thing to do. There is one day between '2019-01-01' and '2019-01-01' according to your own definition.

Since you speak of dates, not timestamps, there's a simpler way:

SELECT date '2019-12-31'
     - date '2019-01-01'
     + 1 AS range;

range
365

Subtracting dates returns integer.
If the input is a timestamp values, just cast to date to truncate the time component:

SELECT ts1::date - ts2::date + 1;