Обсуждение: Timezone With Timestamp

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.

For example when the data is being inserted from east coast our system
does a:

SET TIMEZONE = 'America/New_York' ;

then INSERT INTO table VALUES ( now() );

(abbreviated for explanation)

Anyway what I want to return in one query is the time in/and the
timezone such as

10:15 AM EST
10:30 AM PST
11:25 PM CST
12:00 PM GMT

Can someone give me a pointer as to what direction to poke in, or is
this even possible?

TIA,

Gavin

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.

I think the easiest way to do what you want is to use timestamp without
time zone and store the timezone in a separate field.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"La realidad se compone de muchos sueños, todos ellos diferentes,
pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos)

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