CODEWITHSUNDEEP
postgresqldate-arithmeticsql-date-functions
coterobarros
coterobarros

Reputation: 1237

Calculating the number of days between two dates in PostgreSQL

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?

Upvotes: 2

Views: 1579

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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;

Upvotes: 3

Adrian Klaver
Adrian Klaver

Reputation: 19665

A roundabout way:

select 
   count(*) AS days 
from 
   generate_series('2019-1-1 00:00:00'::timestamp, 
                   '2019-12-31 00:00:00'::timestamp, '1 day');
 days 
------
  365

Upvotes: 1

madbird
madbird

Reputation: 1379

You probably meant

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

because it's absolutly correct, that at 00:00:00 Dec 31 - right after 23:59:59 Dec 30 - it's only 364 full days passed.

Upvotes: 1

Related Questions