You could store the zone in a separate field and then create a VIEW on
the table that used a function to take both values and return the
timestamptz just as it was inserted.
Well no, it is not possible. A timestamptz value is interpreted as UTC,
regardless of your local timezone. A timestamp value is interpreted in
your local time zone. This is the main difference between them. You can
change *the interpretation* of these values with the "at time zone"
expression. But you cannot convert between time zones at all! Time zone
information is not stored in any way - it is a global setting.
I have intentionally chosen an example where the local time is changed
from summer time to winter time (e.g. local time suddenly "goes back"
one hour). It demonstrates that you cannot use "at time zone ...."
expression to convert a timestamptz into a desired time zone manually.
The only case when time zone conversion occurs is when you format the
timestamp/timestamptz value into a text. As Tom Lane pointed out, the
only correct way to convert a timestamptz/timestamp value into a desired
time zone is to use the "set time zone to ...." command. But that
command has a global effect, and it does not actually change the zone of
the stored value (because the time zone is not stored at all). It just
changes the formatting of those values, and as a result, you will get a
correct textual representation of the original timestamp value in the
desired time zone. But you will *never* be able to get a correct
timestamp value in a desired time zone. All you can get is text.
As far as I'm concerned, I'm going to set the system's clock to UTC,
store everything in timestamp field (in UTC), and use a program to
convert fetched values before displaying them.
Regards,
Laszlo
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
