Timestamp vs. Interval and formatting....

От:

Dan Jewett <dan@thenormalfamily.net>

Дата:

4 января 2004 г. в 16:34:51

Hello,

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?

I noticed that the "to_char" function for intervals is deprecated.
Is timestamp a better choice for this field after all?

Thanks,
Dan J.

_________________
Visit Eva, Anne, and Dan at: http://www.thenormalfamily.net.

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

От:

Dan Jewett <dan@thenormalfamily.net>

Дата:

4 января 2004 г. в 21:31:27

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

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

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

От:

Michael Glaesemann <grzm@myrealbox.com>

Дата:

4 января 2004 г. в 16:53:40

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.



Does this help?

Michael Glaesemann
grzm myrealbox com

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

От:

Michael Glaesemann <grzm@myrealbox.com>

Дата:

5 января 2004 г. в 02:14:40

Hi Dan,

On Jan 4, 2004, at 7:30 PM, Dan Jewett wrote:

> On Sun, 4 Jan 2004 14:47:49 -0600, Michael Glaesemann wrote this well
> considered message:

You certainly know how to flatter a guy! :)

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

This is just thinking out loud: I haven't tried it. Can you do 
something like this?

UPDATE track_length SET length = (
	SELECT EXTRACT(hour FROM length)::min + EXTRACT(min FROM length)::sec
	FROM track_length);

I'm not sure about the casts as they are, but there should be a way to 
do that. There was just a post that had a subquery used to get a value 
on a update, so I'm not sure that'd work either, but might be worth a 
shot.

FWIW,

Michael Glaesemann
grzm myrealbox com

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

От:

Michael Glaesemann <grzm@myrealbox.com>

Дата:

5 января 2004 г. в 02:23:32


On Jan 5, 2004, at 12:13 AM, Michael Glaesemann wrote:

> Hi Dan,
>
> On Jan 4, 2004, at 7:30 PM, Dan Jewett wrote:
>
>> On Sun, 4 Jan 2004 14:47:49 -0600, Michael Glaesemann wrote this well
>> considered message:
>
> You certainly know how to flatter a guy! :)
>
>> Now I just have the issue of resetting all those times I already
>> entered which were HH:MM instead of MM:SS.
>
> This is just thinking out loud: I haven't tried it. Can you do 
> something like this?
>
> UPDATE track_length SET length = (
> 	SELECT EXTRACT(hour FROM length)::min + EXTRACT(min FROM length)::sec
> 	FROM track_length);

On second thought, I think this SELECT isn't specific enough. This 
should be better:

UPDATE track_length
SET length = (EXTRACT(hour FROM length)::min + EXTRACT(min FROM 
LENGTH)::sec)

Still not sure of the casts, but I think it's closer.

Michael Glaesemann
grzm myrealbox com