Re: Messed up time zones - Mailing list pgsql-admin

On 08/03/2012 08:23 AM, Laszlo Nagy wrote:
> ...
>
> It works. Thank you!
>
> So is it impossible to construct a query with columns that are
> different time zones? I hope I'm not going to need that. :-)
>

I'm not sure you have internalized the meaning of timestamptz. It helps
to instead think of it as a "point in time", i.e. the shuttle launched at...

select
now() at time zone 'UTC' as "UTC",
now() at time zone 'Asia/Urumqi' as "Urumqi",
now() at time zone 'Asia/Katmandu' as "Katmandu",
now() at time zone 'America/Martinique' as "Martinique",
now() at time zone 'America/Kralendijk' as "Kralendijk",
now() at time zone 'Africa/Algiers' as "Algiers",
now() at time zone 'Europe/Zurich' as "Zurich",
now() at time zone 'Australia/Brisbane' as "Brisbane",
now() at time zone 'Pacific/Galapagos' as "Galapagos"
;

-[ RECORD 1 ]--------------------------
UTC        | 2012-08-03 15:54:49.645586
Urumqi     | 2012-08-03 23:54:49.645586
Katmandu   | 2012-08-03 21:39:49.645586
Martinique | 2012-08-03 11:54:49.645586
Kralendijk | 2012-08-03 11:54:49.645586
Algiers    | 2012-08-03 16:54:49.645586
Zurich     | 2012-08-03 17:54:49.645586
Brisbane   | 2012-08-04 01:54:49.645586
Galapagos  | 2012-08-03 09:54:49.645586

All the above are the exact same point in time merely stated as relevant
to each location. Note that given a timestamp with time zone and a zone,
PostgreSQL returns a timestamp without time zone (you know the zone
since you specified it). Conversely, given a local time (timestamp with
out time zone) and a known location you can get the point in time
(timestamptz):

select
'2012-08-03 15:54:49.645586 UTC'::timestamptz,
'2012-08-03 15:54:49.645586 Asia/Urumqi'::timestamptz,
'2012-08-03 15:54:49.645586 Asia/Katmandu'::timestamptz,
'2012-08-03 15:54:49.645586 America/Martinique'::timestamptz,
'2012-08-03 15:54:49.645586 America/Kralendijk'::timestamptz,
'2012-08-03 15:54:49.645586 Africa/Algiers'::timestamptz,
'2012-08-03 15:54:49.645586 Europe/Zurich'::timestamptz,
'2012-08-03 15:54:49.645586 Australia/Brisbane'::timestamptz,
'2012-08-03 15:54:49.645586 Pacific/Galapagos'::timestamptz
;

-[ RECORD 1 ]------------------------------
timestamptz | 2012-08-03 08:54:49.645586-07
timestamptz | 2012-08-03 00:54:49.645586-07
timestamptz | 2012-08-03 03:09:49.645586-07
timestamptz | 2012-08-03 12:54:49.645586-07
timestamptz | 2012-08-03 12:54:49.645586-07
timestamptz | 2012-08-03 07:54:49.645586-07
timestamptz | 2012-08-03 06:54:49.645586-07
timestamptz | 2012-08-02 22:54:49.645586-07
timestamptz | 2012-08-03 14:54:49.645586-07

I'm currently in Pacific Daylight Time hence the -07. But note that you
can specify an offset (-07) that is not the same as
'America/Los_Angeles'. -07 is an offset, 'America/Los_Angeles' is a time
zone and deals appropriately with Daylight Saving Time and the various
changes thereto through history.

Should it be necessary, you could save time zone information in a
separate column. Note that you can specify time zone as a characteristic
of a user if your database handles users across multiple zones (alter
user steve set timezone to 'America/Los_Angeles';)

It takes a bit of reading and experimenting to understand the subtleties
of date/time handling but it's time well spent.

Cheers,
Steve