Number of days in a daterange

user_754176 0
Viewed 671

How do you extract the number of days from a column of type daterange? Is it possible?

I tried 

select age(daterange('2018-01-01', '2018-02-01'));

But it fails, I also tried casting to int with out luck. I reviewed the docs but I was unable to find any function that could acomplish this in the range types.

postgresql
2 Answers

We can try using the upper() and lower() functions to find the upper and lower bound of the date range. Then, take the difference of these dates to find the number of days in between them:

SELECT
    UPPER(daterange('2018-01-01', '2018-02-01')) -
        LOWER(daterange('2018-01-01', '2018-02-01')) AS diff;

31

Note that the way you constructed your range is actually identical to this:

daterange('2018-01-01', '2018-02-01', '[)')

That is, the range is upper bound exclusive. As a result, only the 31 days of January 2018 are included in the count, but 1st February is excluded. If you instead wanted to include that date point as well, you could use this range:

daterange('2018-01-01', '2018-02-01', '[]')

Running the above query would then yield a day count of 32.

user_18632290

I believe this should work:

SELECT DATE_PART('day', '2018-01-01'::timestamp - '2018-02-01'::timestamp);

[EDIT]

using daterange, this will work:

SELECT upper(daterange('2018-01-01', '2018-02-01')) - lower(daterange('2018-01-01', '2018-02-01'))
user_93218160
Related