Обсуждение: 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.

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

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