Re: convert column of integer type to time type?

[Michael Glaesemann <grzm(at)seespotcode(dot)net>]

On Jun 27, 2007, at 10:36 , Carol Cheung wrote:

> My question is: Is it possible to alter the 'some_int' column  
> directly without having to create a temporary 'some_time' holding  
> column?

Are you trying to alter the table to replace your some_int column  
with a some_time column? I believe you can do this in two steps.

SELECT *
FROM some_data;
some_int
----------
       12
      345
     1622
        1
(4 rows)

ALTER TABLE some_data
ALTER some_int TYPE TIME WITHOUT TIME ZONE
      USING CAST(to_char(some_int, 'FM99909:99') AS TIME WITHOUT  
TIME ZONE);

ALTER TABLE some_data
RENAME some_int TO some_time;

SELECT *
FROM some_data;
some_time
-----------
00:12:00
03:45:00
16:22:00
00:01:00
(4 rows)

Hope this helps.

Michael Glaesemann
grzm seespotcode net