Perf differences between timestamp and timestamp with timezone

Mike Christensen wrote:

Hi all -

I'm considering changing all my "timestamp" columns to "timestamp with
timezone" columns instead. The reason is I want to use UTC time for
everything in the DB and on the web server, and only ever convert to local
time on the client itself. I could use a timestamp and just "know" that the
timezone is UTC, but npgsql seems to handle this a lot better (like set the
"Kind" property to UTC, etc) if you have a timestamptz column type.

I'm curious if there's any perf drawbacks to doing this, or would the only
perf hit be if I were comparing timestamp with timestamptz and doing all
sorts of casts and stuff. Thanks!

I don't think there is any measurable difference in the two data types,
and timestamptz is usually superior, as you have found.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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)..

On Mon, Jun 15, 2009 at 6:52 PM, Bruce Momjian <bruce@momjian.us> wrote:

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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