Date: Tue, 29 Oct 2013 11:34:50 +0000
To: pgsql-sql@postgresql.org
Subject: Re: Number of days in a tstzrange?
Message-ID: <20131029113450.GA26067@teak.britvault.co.uk>
References: <20131024204638.GA31094@teak.britvault.co.uk>
 <219E3855-325F-4B55-A23B-DA014FBDA2BF@excoventures.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <219E3855-325F-4B55-A23B-DA014FBDA2BF@excoventures.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
From: skinner@britvault.co.uk (Craig R. Skinner)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 2013-10-24 Thu 17:00 PM |, Jonathan S. Katz wrote:
> 
> Yes, you would have to call lower() and upper() to accomplish that.
> 

Here's my final solution inside a function's CUSROR LOOP:

DECLARE
	billable_days	integer;
	...
	..
BEGIN
	...
	...
	SELECT
		-- cast boundries to date
		-- date - date = integer
		upper(billable_item.billable_period)::date -
			lower(billable_item.billable_period)::date
	INTO
		billable_days;

	-- if a subscription is ceased same day it's started,
	-- that day is still chargable, so bump it
	IF billable_days < 1
	THEN
		billable_days := 1;
	END IF;
	...
	...

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql