From: | Noah Misch <noah(at)leadboat(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | Andres Freund <andres(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | No-rewrite timestamp<->timestamptz conversions |
Date: | 2015-02-06 01:36:18 |
Message-ID: | 20150206013618.GA3640810@tornado.leadboat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Nov 05, 2013 at 05:02:58PM -0800, Josh Berkus wrote:
> I'd also love some way of doing a no-rewrite conversion between
> timestamp and timestamptz, based on the assumption that the original
> values are UTC time. That's one I encounter a lot.
It was such a conversion that motivated me to add the no-rewrite ALTER TABLE
ALTER TYPE support in the first place. Interesting. Support for it didn't
end up in any submitted patch due to a formal problem: a protransform function
shall only consult IMMUTABLE facts, but we posit that timezone==UTC is a
STABLE observation. However, a protransform function can easily simplify the
immutable expression "tscol AT TIME ZONE 'UTC'", avoiding a rewrite. See
attached patch. Examples:
begin;
create table t (c timestamptz);
set client_min_messages = debug1;
-- rewrite: depends on timezone GUC
alter table t alter c type timestamp;
-- rewrite: depends on timezone GUC
alter table t alter c type timestamptz;
-- no rewrite: always UTC+0
alter table t alter c type timestamp using c at time zone 'UTC';
-- no rewrite: always UTC+0
alter table t alter c type timestamptz using c at time zone 'Etc/Universal';
-- rewrite: always UTC+0 in the present day, but not historically
alter table t alter c type timestamp using c at time zone 'Atlantic/Reykjavik';
-- rewrite: always UTC+0 in the present day, but not historically
alter table t alter c type timestamptz using c at time zone 'Africa/Lome';
-- no rewrite: always UTC+0
alter table t alter c type timestamp using c at time zone 'GMT';
-- rewrite: always UTC+1
alter table t alter c type timestamptz using c at time zone '1 hour'::interval;
-- no rewrite: always UTC+0
alter table t alter c type timestamp using c at time zone '0 hour'::interval;
rollback;
Attachment | Content-Type | Size |
---|---|---|
at-timezone-v1.patch | text/plain | 9.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2015-02-06 03:06:12 | Re: Table-level log_autovacuum_min_duration |
Previous Message | Naoya Anzai | 2015-02-06 00:50:08 | Re: Table-level log_autovacuum_min_duration |