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@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
