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

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
useful?

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com