Time Zone design issues

Time zones are a new issue for me. I have read around a bit and learned some.
I have a bunch of questions still because I've not found a really good
overview of how all of the factors tie in together.

At this time my app will be hosted on a server at a single location. Users
will be updating and viewing from various time zones. I would like to
present the datetime of the last update to each user according to the time
zone that they've entered into their profile (stored in the same app db).

I'm going to lay out my notions, some may be 'wrong' or too simplistic. It'd
be excellent if anyone knows of a writeup on this.

I see if a field is type timestamptz, the update is tagged with the server's
tz (pst) example 2007-09-09 20:00:17.906-07. Very cool.

I expect the simplest route is to record all updates according to a tz that
does not vary, UTC or GMT.

But it doesn't particularly make sense to set the server's clock to UTC.
It'd seem to skew a lot of server functionality which I'd think should
normally be geared around local time. So I'd guess that the route to take is
to keep the server pegged to local time, and use a function that derives UTC
from now(), tags the 'last modified' fields with that value.

On the application level, when a user views the 'last updated' info, the app
could to adjust the UTC timestamp so that the time data is adjusted from
UTC.

So far I make sense to myself, but would like feedback, I may have too
simple a view or just missed something.

What I am not so sure of is how to coordinate the timezones. It seems like a
complex subject. Most of the timezone data lists include all timezones, with
all dst variations. When one of my app's users is setting the time zone that
applies to them, are they supposed to pick the current time (PDT) or the
base zone they live in (Pacific USA or somesuch). I'd have thought the
second choice. So, they're chosing their physical location w/time zone,
region or whatever it's called. The details of this are
daunting...postgresql comes with a lot of timezone data, in windows for
example

...\postgresql\share\timezonesets\America.txt

And some binary files that I don't get at all like

...\postgresql\share\timezone\US\Pacific

OK, maybe I can work out what to do with those. But it'd be some real work,
and it must have been done many times before. So I'm hoping someone has a
logical, standard way of handling all of this with postgres up on the web
that I can pore over and adjust if needed. I am only going to need North
America for the first year or so, I expect, and could fake my way through
this pretty well. I'd much rather start with a fairly mature scheme in place
so I don't need to redo in later on.

I will understand if this topic is too big of a headache to get into
here...just hoping I get lucky <g>.

--
View this message in context: http://www.nabble.com/Time-Zone-design-issues-tf4411984.html#a12586169
Sent from the PostgreSQL - general mailing list archive at Nabble.com.