From: Jonathan S. Katz <jonathan.katz@excoventures.com>
To: skinner@britvault.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: Number of days in a tstzrange?
Date: Thu, 24 Oct 2013 17:00:36 -0400
Message-ID: <219E3855-325F-4B55-A23B-DA014FBDA2BF@excoventures.com> (raw)
In-Reply-To: <20131024204638.GA31094@teak.britvault.co.uk>
References: <20131024204638.GA31094@teak.britvault.co.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jonathan.katz@excoventures.com, skinner@britvault.co.uk
Subject: Re: Number of days in a tstzrange?
In-Reply-To: <219E3855-325F-4B55-A23B-DA014FBDA2BF@excoventures.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox