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

postgresqltimezone

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?

Best Answer

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.

As of September 2019, this works in 95% of the browsers used globally.

Old compatibility information

ecma-402/1.0 says that timeZone may be undefined if not provided to constructor. However, future draft (3.0) fixed that issue by changing to system default timezone.

In this version of the ECMAScript Internationalization API, the timeZone property will remain undefined if no timeZone property was provided in the options object provided to the Intl.DateTimeFormat constructor. However, applications should not rely on this, as future versions may return a String value identifying the host environment’s current time zone instead.

in ecma-402/3.0 which is still in a draft it changed to

In this version of the ECMAScript 2015 Internationalization API, the timeZone property will be the name of the default time zone if no timeZone property was provided in the options object provided to the Intl.DateTimeFormat constructor. The previous version left the timeZone property undefined in this case.

Java – How to set time zone of a java.util.Date

Use DateFormat. For example,

SimpleDateFormat isoFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
isoFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
Date date = isoFormat.parse("2010-05-23T09:01:02");

Best Answer

Related Solutions

Javascript – Getting the client’s time zone (and offset) in JavaScript

As of September 2019, this works in 95% of the browsers used globally.

Old compatibility information

Java – How to set time zone of a java.util.Date

Related Topic