Postgres Pro
Downloads
Home   >   mailing lists

Re: Number of days in a tstzrange? - Mailing list pgsql-sql

From skinner@britvault.co.uk (Craig R. Skinner)
Subject Re: Number of days in a tstzrange?
Date
Msg-id 20131029113450.GA26067@teak.britvault.co.uk
Whole thread Raw
In response to Re: Number of days in a tstzrange?  ("Jonathan S. Katz" <jonathan.katz@excoventures.com>)
Responses Re: Number of days in a tstzrange?
List pgsql-sql
Tree view 
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:

DECLAREbillable_days    integer;.....
BEGIN......SELECT    -- cast boundries to date    -- date - date = integer
upper(billable_item.billable_period)::date-        lower(billable_item.billable_period)::dateINTO    billable_days;
 
-- if a subscription is ceased same day it's started,-- that day is still chargable, so bump itIF billable_days < 1THEN
  billable_days := 1;END IF;......
 

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

pgsql-sql by date:

Previous
From: David Johnston
Date:
Subject: Re: sum of until (running balance) and sum of over date range in the same query
Next
From: "M. D."
Date:
Subject: Re: Re: sum of until (running balance) and sum of over date range in the same query