Search code examples
postgresqltimezonetimestampdst

Can you recover the full timezone name from a Postgresql TIMESTAMP WITH TIME ZONE field?

Take the following example:

create temporary table t1 (c1 timestamp with time zone);
insert into t1 values ('2003-04-12 04:05:06 America/New_York'::timestamp with time zone);

If I'm reading the documentation correctly, here Postgresql will use the full timezone name to convert the timestamp to UTC by adding +05:00 hours, then store that.

But if that's true then I can't distinguish between 2003-04-12 04:05:06 America/New_York and 2003-04-12 04:05:06 America/Panama, which has the same UTC offset but a different daylight savings offset.

Is that right?

Solution

  • PostgreSQL doesn't store original time zone. You can do it as separate column when you need it.

    http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_III#Domain_for_time_zone

    CITEXT is case insensitive text type from PostgreSQL contrib package. You can use a text instead if you don't want to install it.