alter column from time with time zone to timestamp

Question

I am having trouble changing a column called end_date in a table called key_request from time with time zone to timestamp in my Postgres database . I have tried using the following code:

alter table key_request alter column end_date type timestamp with time zone using end_date::timestamp with time zone

I keep getting the following error:

ERROR:  cannot cast type time with time zone to timestamp with time zone

Any idea of how I can adjust this query to work?

Answer 1

you can do something like this:

alter table key_request
alter column end_date type timestamp with time zone using date('20130101') + end_date;

sql fiddle demo

Answer 2

I woul do this in a series of steps

1. Alter the table, adding a new column end_date1 as time with time zone
2. Copy the date from end_date(old) to end_date1
3. Alter the table, droping the old end_date column
4. Alter the table,reaming end_date1 to end_date