PostgreSQL alter type timestamp without time zone -> with time zone

Question

The question is short: if I already have data in a column type timestamp without time zone, if I set the type to timestamp with time zone, what does postgresql do with this data?

You are right, but until I fire up my application/db/whatever, the answer may come. — gyorgyabraham
@a_horse_with_no_name because "it worked once" is a very BAD way to know what it will do EVERY time! — Philip Couling
@couling: so you are implying that the same set of statements will produce different results if run multiple times? Even if all input parameters are the same? In that case dbhenur's answer would not prove anything because according to you running that statement once does not guarantee that it will work the same way the next time. — a_horse_with_no_name
I'm not implying it I'm stating it. See the given answer! The time zone of your session affects the result. I've seen this catch people out where they put the query into an SQL window (on the command line) and it gives one result, and they use it in their app and it gives another (due to their profile switching the time zone without them knowing). — Philip Couling
Further to this. Consider a SQL query which doesn't have an ORDER BY clause. The order of rows returned is officially undefined; the RDBMS will just return them in whatever order happens to be easiest. I've seen developers mistakenly believe that they can rely on the oldest row (longest since update) to come out first. And they believed this simply because they "just tried it". ...debenhur's answer is correct even though his justification is not solid. References to the manual would be better. — Philip Couling

dbenhur dbenhur · Accepted Answer · 2012-03-19T16:23:30

It keeps the current value in localtime and sets the timezone to your localtime's offset:

create table a(t timestamp without time zone, t2 timestamp with time zone);
insert into a(t) values ('2012-03-01'::timestamp);
update a set t2 = t;
select * from a;
          t          |           t2           
---------------------+------------------------
 2012-03-01 00:00:00 | 2012-03-01 00:00:00-08

alter table a alter column t type timestamp with time zone;
select * from a;
           t            |           t2           
------------------------+------------------------
 2012-03-01 00:00:00-08 | 2012-03-01 00:00:00-08

According to the manual for Alter Table:

if [the USING clause is] omitted, the default conversion is the same as an assignment cast from old data type to new.

According to the manual for Date/Time types

Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.

PostgreSQL alter type timestamp without time zone -> with time zone

2 Answers