Message-ID: <501BFA4F.5010709@pinpointresearch.com>
Date: Fri, 03 Aug 2012 09:20:31 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: Laszlo Nagy <gandalf@shopzeus.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
References: <501B8C6F.6040100@shopzeus.com> <501BAA84.9010306@shopzeus.com>
	<501BAE33.4090102@shopzeus.com> <16399.1344003584@sss.pgh.pa.us>
	<501BED07.5070603@shopzeus.com>
In-Reply-To: <501BED07.5070603@shopzeus.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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