Re: Timestamp vs. Interval and formatting....

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