Number of days in a tstzrange?

From: skinner(at)britvault(dot)co(dot)uk (Craig R(dot) Skinner)
To: pgsql-sql(at)postgresql(dot)org
Subject: Number of days in a tstzrange?
Date: 2013-10-24 20:46:38
Message-ID: 20131024204638.GA31094@teak.britvault.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Cheers,
--
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jonathan S. Katz 2013-10-24 21:00:36 Re: Number of days in a tstzrange?
Previous Message Gary Stainburn 2013-10-21 09:52:47 Re: Re: Advice - indexing on varchar fields where only last x characters known