Convert "time with time zone" to "timestamp with time zone" in Postgresql

Question

i have to alter a table column from "time with time zone" to "timestamp with time zone".

I tryed

alter table mytable
alter column date type timestamp with time zone using date::timestamp with time zone

But i get an error that postgres cannot automatically convert these types. How can i do this? SQLFiddle

http://stackoverflow.com/a/18255845/3682599 or http://stackoverflow.com/a/18255040/3682599 — Vivek S., Sep 12 '14 at 04:49

score 0 · Answer 1 · answered Sep 11 '14 at 17:27

0

You may specify using null as the conversion mechanism to start with empty values:

alter table mytable
  alter column date type timestamp with time zone
  using null;

answered Sep 11 '14 at 17:27

Daniel Vérité

so i have to truncate my table first? Is there a way to covert this column? – 0bmis Sep 11 '14 at 21:08
@0bmis: no need to truncate the rows, but for a conversion you'll have to provide your own method. A time does not contain enough information to make a timestamp (it lacks the date), and postgres cannot invent that information. – Daniel Vérité Sep 11 '14 at 22:53

score 0 · Accepted Answer · answered Sep 12 '14 at 08:08

0

@Winged Panther thank you very much ;) Because time leaks the date its possible to convert with an constant date:

alter table mytable
alter column date type timestamp with time zone using date('20140101') + date;

answered Sep 12 '14 at 08:08

0bmis

2 Answers2