Thread: convert column of integer type to time type?

Hi,

I want to convert a column of type integer to type 'time without time zone'.

If I have a table with an integer column

  some_int
----------
        12
       345
      1622
         1

Then, I add a column to the table of type 'time without time zone'
(some_time).
and, I can perform the update:

UPDATE tester SET some_time = CAST (to_char(some_int, 'FM99909:99') AS
time without time zone);

  some_int | some_time
----------+-----------
        12 | 00:12:00
       345 | 03:45:00
      1622 | 16:22:00
         1 | 00:01:00


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

Thanks in advance,
C

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

Michael,
Thank you for you help.
This solution worked.

On 27/06/2007 12:22, Michael Glaesemann wrote the following:
>
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org