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, Mar 19 '12 at 15:23
@a_horse_with_no_name because "it worked once" is a very BAD way to know what it will do EVERY time! — Philip Couling, Mar 20 '12 at 10:51
@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. — , Mar 20 '12 at 11:44
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, Mar 25 '12 at 16:55
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, Mar 25 '12 at 16:59

score 76 · Accepted Answer · edited Mar 25 '12 at 18:56

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.

Yes. This behavior is exactly the same as casting from time stamp without time zone to time stamp with time zone. If you would like something different you can use `ALTER COLUMN column TYPE type USING ` and use the time functions for handling time zones. http://www.postgresql.org/docs/current/static/functions-datetime.html — Philip Couling, Mar 20 '12 at 11:02

Antti Haapala -- Слава Україні · Answer 2 · 2022-08-29T12:44:44.290

It is better to specify the time zone explicitly. Say, if your timestamps without timezone are supposed to contain the timestamp in the UTC timezone, you should be wary of the fact that the timezone of the client or server might be messing everything here.

The best way is to just specify the timezone to be used explicitly:

ALTER TABLE a_table
    ALTER COLUMN ts_column 
    TYPE TIMESTAMP WITH TIME ZONE
        USING ts_column AT TIME ZONE 'UTC'

(And as always, you may want to run the DDL statements inside a transaction so that you can still revert the change with a single rollback)

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

2 Answers2

Linked