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. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
