Skip site navigation (1) Skip section navigation (2)

Re: Messed up time zones

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Laszlo Nagy <gandalf(at)shopzeus(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Messed up time zones
Date: 2012-08-03 16:20:31
Message-ID: 501BFA4F.5010709@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2012-08-03 16:38:11
Subject: Re: Messed up time zones
Previous:From: Laszlo NagyDate: 2012-08-03 16:06:38
Subject: Re: Messed up time zones

pgsql-admin by date

Next:From: Tom LaneDate: 2012-08-03 16:38:11
Subject: Re: Messed up time zones
Previous:From: Laszlo NagyDate: 2012-08-03 16:06:38
Subject: Re: Messed up time zones

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group