Postgres Pro
Downloads
Home   >   mailing lists

Thread: Number of days in a tstzrange?

Number of days in a tstzrange?

From
skinner@britvault.co.uk (Craig R. Skinner)
Date:
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?

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

Re: Number of days in a tstzrange?

From
"Jonathan S. Katz"
Date:
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

Re: Number of days in a tstzrange?

From
skinner@britvault.co.uk (Craig R. Skinner)
Date:
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

Re: Number of days in a tstzrange?

From
skinner@britvault.co.uk (Craig R. Skinner)
Date:
On 2013-10-29 Tue 11:34 AM |, Craig R. Skinner wrote:
> 
>     -- 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;
>     ...
>     ...
> 

This is more accurate:
-- If a subscription billing_period ends part way through a day,IF upper(billable_item.billable_period)::time <> time
'allballs'THEN   -- That partial day is still chargable, so bump it    billable_days := billable_days + 1;END IF;

Re: Number of days in a tstzrange?

From
Gavin Flower
Date:
On 30/10/13 23:40, Craig R. Skinner wrote:
> On 2013-10-29 Tue 11:34 AM |, Craig R. Skinner wrote:
>>     -- 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;
>>     ...
>>     ...
>>
> This is more accurate:
>
>     -- If a subscription billing_period ends part way through a day,
>     IF upper(billable_item.billable_period)::time <> time 'allballs'
>     THEN
>         -- That partial day is still chargable, so bump it
>         billable_days := billable_days + 1;
>     END IF;
>
>
>
even for 1 second over???

I would expect any court of law to set a higher threshold!


Cheers,
Gavin