Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37521=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1VZS1M-0007eQ-Rd
 for pgsql-sql@arkaria.postgresql.org; Thu, 24 Oct 2013 21:00:45 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37521=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1VZS1M-0005sB-BF
 for pgsql-sql@arkaria.postgresql.org; Thu, 24 Oct 2013 21:00:44 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jonathan.katz@excoventures.com>) id 1VZS1L-0005s5-E3
 for pgsql-sql@postgresql.org; Thu, 24 Oct 2013 21:00:43 +0000
Received: from mail-qe0-f46.google.com ([209.85.128.46])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jonathan.katz@excoventures.com>) id 1VZS1I-0001S4-LL
 for pgsql-sql@postgresql.org; Thu, 24 Oct 2013 21:00:42 +0000
Received: by mail-qe0-f46.google.com with SMTP id s14so1793132qeb.5
 for <pgsql-sql@postgresql.org>; Thu, 24 Oct 2013 14:00:39 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:subject:mime-version:content-type:from
 :in-reply-to:date:cc:content-transfer-encoding:message-id:references
 :to; bh=SCHfmEep7p+8HjsjKMJSV1VjKrvGSBZ8WwvsK3jamWE=;
 b=f+jtXjh0rgMMqt0Ll3D4PdQnIJxTx4iVajOh5VzK2jWao54KErlntQDbd91oAdFBH+
 FWZlInFM17t/vc46CKbMi6IHIjsZNuMBFaddO6KAtWaoDiunKtwWPhhkyN7HErXTRk8C
 pSfLKI8JJv6WpKXCaaqdGZem+MiuoBmp3B/bGTHAzI2K446IT6ZNbOcXbBQOw0ztBMlP
 bZO8px7DUprNljtdlEtVaZfP8B+fiWv2WdxYdjyp8YD9AcyI3E8SGoa9PeIKKtUe79pk
 EwFtJSrg0KMj/4qep9YKZzuGx+fBUk/LwI55o55pYvPy7hRSU4YqxBeLcybLO+C0mG09
 KKIg==
X-Gm-Message-State: 
 ALoCoQmwGW1zy8TrJ2+r5muxA36P/g6n27e3KJ5y7mMp2VmVDMD01nUBiIgRJfSuPRA5rbUqQMrq
X-Received: by 10.49.95.135 with SMTP id dk7mr6207619qeb.3.1382648439424;
 Thu, 24 Oct 2013 14:00:39 -0700 (PDT)
Received: from [10.0.0.17] (pool-72-69-127-84.nycmny.fios.verizon.net.
 [72.69.127.84])
 by mx.google.com with ESMTPSA id u3sm6418110qej.8.2013.10.24.14.00.38
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 24 Oct 2013 14:00:38 -0700 (PDT)
Subject: Re: Number of days in a tstzrange?
Mime-Version: 1.0 (Apple Message framework v1283)
Content-Type: text/plain; charset=us-ascii
From: "Jonathan S. Katz" <jonathan.katz@excoventures.com>
In-Reply-To: <20131024204638.GA31094@teak.britvault.co.uk>
Date: Thu, 24 Oct 2013 17:00:36 -0400
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
Message-Id: <219E3855-325F-4B55-A23B-DA014FBDA2BF@excoventures.com>
References: <20131024204638.GA31094@teak.britvault.co.uk>
To: skinner@britvault.co.uk (Craig R. Skinner)
X-Mailer: Apple Mail (2.1283)
X-Pg-Spam-Score: -0.7 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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