2

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?

Improve this question
0

3 Answers 3

Reset to default
3

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

8 Comments

Yes, I saw that even 1s left is enough to get the wrong 30 days value in select date_part('day', '2019-1-31 23:59:59'::timestamp - '2019-1-1 00:00:00'::timestamp);
Using dates won't help either: select '2019-12-31'::date - '2019-1-1 00:00:00'::date; 364 as I'm pretty sure the dates get cast to a timestamp as midnight.
Casting to date means setting time to '00:00:00', I suppose.
I also found this sentence, but renders 364 days too for a normal year select date '2019-12-31' - date '2019-1-1';
Again: this actually is 364 days, the 365th day - 2019-12-31 - is not in that range
|
1

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
Improve this answer

4 Comments

Good try Adrian, but it fails for two identical dates or for two near ones. Check select count(*) AS days from generate_series('2019-1-1 00:00:00'::timestamp, '2019-1-1 00:00:00'::timestamp, '1 day');
I am assuming that a conditional if maybe unavoidable for two identical dates.
Basically the idea is calculating the doy (day of year) of both dates and take the difference between those two integer. If you meant to have an inclusive solution you should add +1 to that difference but then it is when the case of two identical dates fails rendering 1. You probably need a condition using a where clause or an if clause in plpgsql
Sorry, I meant when, not where.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.