Re: Number of days in a tstzrange? - Mailing list pgsql-sql

On Oct 24, 2013, at 4:46 PM, Craig R. Skinner wrote:

> Hi folks,
> 
> How can the number of days contained within a range be found? (9.2)
> 
> For example, with these timestamp ranges,
>             get these (integer) number of days:
> 
> tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 (day)
> tstzrange('2013-10-01 07:00', '2013-10-01 23:45') | 1 (day)
> tstzrange('2013-10-01 02:00', '2013-10-02 23:45') | 2 (days)
> tstzrange('2013-10-01 07:00', '2013-10-03 01:00') | 2 (days)
> tstzrange('2013-10-01 01:00', '2013-10-03 23:00') | 3 (days)
> tstzrange('2013-10-01 23:00', '2013-10-04 01:00') | 4 (days)
> 
> In my digging about, I've not found a builtin function for this.
> 
> Is is necessary pull out the lower() and upper() timestamp elements,
> then get the date interval between them?

Yes, you would have to call lower() and upper() to accomplish that.

Jonathan