Обсуждение: Perf differences between timestamp and timestamp with timezone
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!
Mike
PS - If there's any way to make npgsql just manufacture UTC DateTime objects to begin with, that would be nice too.. Digging through the code I coudn't find an easy way.
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!
Mike
PS - If there's any way to make npgsql just manufacture UTC DateTime objects to begin with, that would be nice too.. Digging through the code I coudn't find an easy way.
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)..
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:I don't think there is any measurable difference in the two data types,
> 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!
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. +
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. +