Skip site navigation (1) Skip section navigation (2)

Re: Regarding interval conversion functions and a seeming lack of usefulness

From: Erik Jones <erik(at)myemma(dot)com>
To: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
Cc: Bill Moran <wmoran(at)collaborativefusion(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Regarding interval conversion functions and a seeming lack of usefulness
Date: 2008-02-28 15:35:21
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On Feb 28, 2008, at 6:04 AM, Alban Hertroys wrote:

> On Feb 27, 2008, at 3:47 PM, Bill Moran wrote:
>> Something like:
>> $ SELECT CONVERT('12 days 13 hours'::INTERVAL AS hour);
>>  hour
>> ------
>>    301
>> $ SELECT CONVERT('6 hours 17 minutes'::INTERVAL AS hour);
>>  hour
>> ------
>> 6.2833
>> Am I approaching this problem wrong? or is there something out there
>> and my Google skills are lacking?
> One of the obvious problems with this is that you cannot convert  
> months to something more fine-grained without knowing the date the  
> interval is relative to. I mean, what would be the answer of:
> $ SELECT CONVERT('2 months'::INTERVAL AS days);
> This month that would be 60 days, next month 61, this month next  
> year 59, etc.
> And I haven't even started on leap seconds and daylight saving time  
> yet...

Typically, even having the option to use functions such of these with  
"standard" measurments (i.e. 30 days = 1 month, 365 days = 1 year,  
etc...) is often really useful.  Observe that the justify_days,  
justify_hours and justify_interval already work with 30 days and 24  
hour increments, respectively, they just don't give you much control  
over the units used in the return value.  In fact, I'd even say that  
the names of justify_days and justify_hours are confusing.  Perhaps  
something like justify_to(some_interval, some_time_unit) would be  

Erik Jones

DBA | Emma®
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at

In response to

pgsql-general by date

Next:From: conor.mccarthyDate: 2008-02-28 15:55:38
Subject: initlocation on windows
Previous:From: Erik JonesDate: 2008-02-28 15:22:11
Subject: Re: disabling triggers, constaints and so on

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group