Alvaro Herrera <alvherre@atentus.com> writes:
> Gavin Roy dijo:
>> I know I'm missing something somewhere but I am using the data type
>> timezone with timestamp and when I am retrieving the data while using
>> the to_char function to format the timestamp, when I include the
>> timezone ( to_char(t_timestamp, 'HH12:MM AM TZ') ) it is returning my
>> local timezone and not the timezone of the record that inserted it.
> Timestamps are stored in GMT and converted to the timezone the client is
> currently in.
In fact, the whole underlying concept of this datatype is that there is
One True Absolute Time Measure. Which you can think of as GMT if you
like, but that's just as arbitrary a point on the earth's circumference
as any other. Timestamp's theory of the world is that you put in a time
that is relative to your local timezone, then the system converts that
to the One True Absolute Time and stores it. Sometime later when some
other person retrieves the time value, it gets converted to their local
timezone for display.
There are many situations where this is the perfectly right mindset
to work in, and I hope I don't need to explain that. But there are
cases where it isn't right, and if that's your situation then you
do not want to use timestamp with timezone. Timestamp without
timezone might work for you instead --- it does absolutely no
conversions for client's local timezone, but just stores and returns
a number that is formatted according to common conventions for a
date/time value. Any timezone semantics that you might want to
attach to the number are your responsibility to store separately.
Does that make any sense? Basically, timestamp with timezone takes
responsibility for converting between different clients' local time
zones, timestamp without time zone doesn't ...
regards, tom lane