agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: skinner@britvault.co.uk
To: pgsql-sql@postgresql.org
Subject: Re: Number of days in a tstzrange?
Date: Tue, 29 Oct 2013 11:34:50 +0000
Message-ID: <20131029113450.GA26067@teak.britvault.co.uk> (raw)
In-Reply-To: <219E3855-325F-4B55-A23B-DA014FBDA2BF@excoventures.com>
References: <20131024204638.GA31094@teak.britvault.co.uk>
	<219E3855-325F-4B55-A23B-DA014FBDA2BF@excoventures.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (5+ messages)  latest in thread

Message-ID: <20131029113450.GA26067@teak.britvault.co.uk>
Permalink:  ../20131029113450.GA26067@teak.britvault.co.uk/
Also on:    postgresql.org/message-id/20131029113450.GA26067@teak.britvault.co.uk

reply

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: skinner@britvault.co.uk
  Subject: Re: Number of days in a tstzrange?
  In-Reply-To: <20131029113450.GA26067@teak.britvault.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox