Postgres Pro
Downloads
Home   >   mailing lists

Thread: VARCHAR to TIME

VARCHAR to TIME

From
"alex b."
Date:
hi,


I have a couple thousand mp3s with all their properties stored in a
table... the songlength is currently saved as varchar... now, after the
database is filled the thought crossed my mind to get the overall
playlength... well, a "sum()" won't work on varchars, obviously...

now, I'd like to port the varchars to the time format.. could someone
give me hints on accomplishing this task?

TIA


  length      | character varying(10)  |


  SELECT length FROM tmp3 ;
...
  02:11
  04:19
  02:04
  03:36
  04:09
  04:08
  01:55
...

Re: VARCHAR to TIME

From
Darren Ferguson
Date:
oss=> \d test1
            Table "public.test1"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 time   | character varying(10) |

oss=> select * from test1;
   time
----------
 03:23:21
(1 row)

oss=> select time::TEXT::INTERVAL from test1;
   time
----------
 03:23:21
(1 row)

Provided you know all values will be of interval type the above will
work for you.
HTH
Darren Ferguson



alex b. wrote:

> hi,
>
>
> I have a couple thousand mp3s with all their properties stored in a
> table... the songlength is currently saved as varchar... now, after
> the database is filled the thought crossed my mind to get the overall
> playlength... well, a "sum()" won't work on varchars, obviously...
>
> now, I'd like to port the varchars to the time format.. could someone
> give me hints on accomplishing this task?
>
> TIA
>
>
>  length      | character varying(10)  |
>
>
>  SELECT length FROM tmp3 ;
> ...
>  02:11
>  04:19
>  02:04
>  03:36
>  04:09
>  04:08
>  01:55
> ...
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Re: VARCHAR to TIME

From
Dennis Gearon
Date:
What are you using as a serving and streaming language? (not related to your question, I want to set up my MP3's like
youhave, and do it on my laptop) 

alex b. wrote:
> hi,
>
>
> I have a couple thousand mp3s with all their properties stored in a
> table... the songlength is currently saved as varchar... now, after the
> database is filled the thought crossed my mind to get the overall
> playlength... well, a "sum()" won't work on varchars, obviously...
>
> now, I'd like to port the varchars to the time format.. could someone
> give me hints on accomplishing this task?
>
> TIA
>
>
>  length      | character varying(10)  |
>
>
>  SELECT length FROM tmp3 ;
> ...
>  02:11
>  04:19
>  02:04
>  03:36
>  04:09
>  04:08
>  01:55
> ...
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>