alter column from time with time zone to timestamp

sql postgresql timestamp postgresql-9.2 alter
wilty picture wilty · Aug 15, 2013 · Viewed 14.2k times · Source

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

Roman Pekar picture Roman Pekar · Aug 15, 2013

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

Related questions

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

I want to extract just the date part from a timestamp in PostgreSQL. I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value. For example, if I have 2011/05/26 09:00:00, …

Read More
CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)? SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 Is there anything like this: SELECT CURRENT_MILLISEC --> 1331255526000 or the …

Read More
Timestamp Difference In Hours for PostgreSQL

Is there a TIMESTAMPDIFF() equivalent for PostgreSQL? I know I can subtract two timestamps to get a postgresql INTERVAL. I just want the difference between the two timestamps in in hours represented by an INT. I can do this in …

Read More