Re: Timestamp vs. Interval and formatting.... - Mailing list pgsql-novice

From Dan Jewett
Subject Re: Timestamp vs. Interval and formatting....
Date
Msg-id 20040104203032280034.GyazMail.dan@thenormalfamily.net
Whole thread Raw
In response to Re: Timestamp vs. Interval and formatting....  (Michael Glaesemann <grzm@myrealbox.com>)
Responses Re: Timestamp vs. Interval and formatting....
List pgsql-novice
On Sun, 4 Jan 2004 14:47:49 -0600, Michael Glaesemann wrote this well
considered message:
> Hi Dan,
>
> On Jan 4, 2004, at 2:11 PM, Dan Jewett wrote:
>> I've been building an application to catalog my CD collection, and have
>> been merrily adding the track lengths to a field of type "interval".
>> This seemed to make the most sense if I wanted to do math on them  (ie.
>> totals) later on.  Everything was working well until I realized that
>> tracks over 24 minutes were being returned as "1 day HH:MM".  What is
>> the easiest way to limit the field to MM:SS?
>
> As you've noticed, your intention of inserting MM:SS is being
> interpreted by PostgreSQL as HH::MM instead. Here's what worked for
> me:
>
> test=# create table track_length (trackid int, length interval);
> CREATE TABLE
> test=# insert into track_length values (1,'1 min 32 sec');
> INSERT 1196294 1
> test=# select * from track_length;
>  trackid |  length
> ---------+----------
>        1 | 00:01:32
> (1 row)
>
> Your system is probably more complex than this, but you can see how
> it works. Check out the following link for more details.
>
>
<http://www.postgresql.org/docs/current/static/datatype-datetime.html#AEN4289>
>
> Does this help?
>
> Michael Glaesemann
> grzm myrealbox com
>

Hello Michael,

Yes I had seen the scenario you describe in the docs, and I was hoping
I wouldn't have to add the step of exploding the time string on the
colon in PHP in order to set up the 'xx min xx sec' string for
Postgres.  Your post did tip me off to the shortcut of simply setting
up the length variable thusly:

$trk_length = "00:" . $length;

Postgres accepts HH:MM:SS for the interval type so you don't have to
explicitly name the units.  Since all the times coming from iTunes
(where I'm getting the info) are in the format MM:SS, I just needed to
prepend them with the HH part.

Now I just have the issue of resetting all those times I already
entered which were HH:MM instead of MM:SS.

Thanks,
Dan J.

pgsql-novice by date:

Previous
From: Aarni Ruuhimäki
Date:
Subject: Re: install
Next
From: Tom Lane
Date:
Subject: Re: multiline PL/Python function fails in *.sql