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

This has come up a few times over the last few months, and I'm not
too keen on the solutions we've been using.

There seems to be a lack of useful functions for converting intervals
to useful representations.  For example, I want to display an interval
in hours and fractions of hours only, not hours and minutes.  There
are lots of examples of when certain representations are more appropriate
than others (think pregnancy terms, why on earth is the woman never
pregnant for 3 months?  It's always 12 weeks!)

Anyway, rant aside, I can't seem to find anything to do this in PG.
I could write a stored procedure easily enough, but I want to make
sure I'm not reinventing the wheel first (I find it hard to believe
I'm the first person ever wanting to do this!)

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?

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Bill Moran <wmoran@collaborativefusion.com> writes:
> There seems to be a lack of useful functions for converting intervals
> to useful representations.  For example, I want to display an interval
> in hours and fractions of hours only, not hours and minutes.

Perhaps EXTRACT(EPOCH ...) to get total seconds and then do some
arithmetic?

            regards, tom lane

In response to Tom Lane <tgl@sss.pgh.pa.us>:

> Bill Moran <wmoran@collaborativefusion.com> writes:
> > There seems to be a lack of useful functions for converting intervals
> > to useful representations.  For example, I want to display an interval
> > in hours and fractions of hours only, not hours and minutes.
>
> Perhaps EXTRACT(EPOCH ...) to get total seconds and then do some
> arithmetic?

Well, that's more or less what I've been doing (although the EPOCH
thing shortened the code up a bit, thanks!).

It just seemed like this would be something so common that there'd
be something in existence already.  I guess I was wrong.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Bill Moran wrote:

> It just seemed like this would be something so common that there'd
> be something in existence already.  I guess I was wrong.

Yeah, I have wished for the same thing myself.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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...

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47c69dd2233091191611641!

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@myemma.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