Thread: timestamp with time zone, retrieving input offset/timezone

On Tue, Aug 4, 2009 at 5:09 AM, Der Tung<dertung@gmx.de> wrote:
> Hello there,
>
> We are creating an Application that needs to handle timestamps in different
> timezones in particular:
>
> Input and output in timezone of the User is no problem with
>
> Set Time Zone and At Time Zone
>
> But know I have two Users A and B in different  timezones.
>
> When A saves a timestamp I want B to:
>
> -          Get the timestamp displayed in his timezone
>
> -          Get the timestamp displayed in the timezone a originally saved it
> in
>
>
>
> Does the type “timestamp with time zone” contain the original timezone or at
> least the offset to UTC it was saved in, or does it just save the UTC time?

Just UTC time.
If you want the offset saved, you'll have to do that yourself.

On 4 Aug 2009, at 13:09, Der Tung wrote:

> But know I have two Users A and B in different  timezones.
> When A saves a timestamp I want B to:
> -          Get the timestamp displayed in his timezone
> -          Get the timestamp displayed in the timezone a originally
> saved it in
>
> Does the type “timestamp with time zone” contain the original
> timezone or at least the offset to UTC it was saved in, or does it
> just save the UTC time?
>
> If it saves the timezone/offset, how can I access it?


No it doesn't, but if you store User A's timestamp as both a timestamp
with time zone and as timestamp witout time zone you should get just
the values you're looking for.

=> select cast(now() at time zone 'CEST' as timestamp without time
zone) at time zone 'GMT';
            timezone
-------------------------------
  2009-08-06 14:15:07.444748+02
(1 row)

=> select cast(now() at time zone 'CEST' as timestamp with time zone)
at time zone 'GMT';
          timezone
---------------------------
  2009-08-06 10:15:12.66097
(1 row)


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a7aadf610131061822158!