Re: Perf differences between timestamp and timestamp with timezone

Mike Christensen wrote:
> Awesome! One more followup question..
>
> If I modify an existing table from timestamp to timestamptz, will it use the
> current system timezone? If so, how can I modify all the rows to convert to
> UTC time (basically add 8 hrs to everything)..

I think you just cast it to timestamp with time zone and it works:

test=> create table test(x timestamp without time zone);
CREATE TABLE
test=> insert into test values (current_timestamp);
INSERT 0 1
test=> select * from test;
x
----------------------------
2009-06-15 22:47:30.608331
(1 row)

test=> alter table test alter column x type timestamp with time zone;
ALTER TABLE
test=> select * from test;
x
-------------------------------
2009-06-15 22:47:30.608331-04
(1 row)

test=> \d test
Table "public.test"
Column | Type | Modifiers
--------+--------------------------+-----------
x | timestamp with time zone |

The beauty of the with time zone data type is the fact it changes as
your timezone changes, rather than being a static date/time:

test=> show timezone;
TimeZone
------------
US/Eastern
(1 row)

test=> set timezone = 'US/Pacific';
SET
test=> select * from test;
x
-------------------------------
2009-06-15 19:47:30.608331-07
(1 row)

Internally it is now UTC but it changes based on your timezone setting.

FYI, we would have liked TIMESTAMP to default to WITH TIMEZONE, but the
SQL standard says the default is WITHOUT TIMEZONE.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +