Thread: 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.
novnov <novnovice@gmail.com> writes: > 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). This is trivial. The column should be timestamptz, and you set the PG timezone parameter to the user's preferred zone within each user's session. Under the hood, the data stored in the timestamptz column is "really" UTC, and conversion to and from the user's preferred zone happens automatically when the value is input or output. You should absolutely not try to fake this by skewing the server's clock, nor by doing "manual" timezone conversions --- any such hack will lead to great pain. > 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. Yeah, there are a lot of 'em :-( Feel free to filter the set of timezone names you make visible to your users, if you have a good idea which ones are really likely to be interesting to them. There was some discussion of that a couple weeks ago IIRC. regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 09/09/07 22:29, novnov wrote: [snip] > > 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. Your single-user Windows mindset is shining brightly. Unix servers have had their internal clocks set to UTC for a decade or more, and there have been no noticeable ill effects, since apps all know to adjust for TZ. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG5MfXS9HxQb37XmcRAuf5AKDKm9h0AxznSTJ0fJx7KzVqFDblYACfeSUV Lub89IZdWSIfvGhUZde/jG0= =3+7a -----END PGP SIGNATURE-----
Ha ha that's a funny and accurate way of putting it! I am deploying on a linux box, so it's good to know this. But 'apps all know to adjust for TZ'; really? In this case I'm creating the app, I can't imagine that it will automatically know to adjust for TZ? The postgres function now() would output now as far as the server is concerned (per it's local time setting I suppose), but very surprising is there is enough magic to report to user time as adjusted to their time zone as recorded in the db? Ron Johnson wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 09/09/07 22:29, novnov wrote: > [snip] >> >> 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. > > Your single-user Windows mindset is shining brightly. > > Unix servers have had their internal clocks set to UTC for a decade > or more, and there have been no noticeable ill effects, since apps > all know to adjust for TZ. > > - -- > Ron Johnson, Jr. > Jefferson LA USA > > Give a man a fish, and he eats for a day. > Hit him with a fish, and he goes away for good! > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFG5MfXS9HxQb37XmcRAuf5AKDKm9h0AxznSTJ0fJx7KzVqFDblYACfeSUV > Lub89IZdWSIfvGhUZde/jG0= > =3+7a > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > -- View this message in context: http://www.nabble.com/Time-Zone-design-issues-tf4411984.html#a12594326 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Mon, Sep 10, 2007 at 07:46:17AM -0700, novnov wrote: > But 'apps all know to adjust for TZ'; really? In this case I'm creating the > app, I can't imagine that it will automatically know to adjust for TZ? The > postgres function now() would output now as far as the server is concerned > (per it's local time setting I suppose), but very surprising is there is > enough magic to report to user time as adjusted to their time zone as > recorded in the db? Look up the definition of the time() function. In UNIX time is measured in seconds since 1 Jan 1970 UTC. This is a number that is obvisouly the same no matter where you are in the world. The C library has a function called localtime() that does all the magic of converting to whatever you consider local time. The postgres function now() is the same: it returns a number of seconds. Only when you actually go to display it does it do the localtime() dance (internally) to make it look nice. That why you can set the timezone to whatever you like and everything works the same. I can log into a number of servers in the world and I just have it setup to export TZ="my timezone" and everything is in my timezone, no matter where the machine is or whatever anyone else on the machine is doing. It's not magic, just someone 30 years ago making the smart choice. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Yes, but there are still some parts of this I don't understand. From the application user's perspective, the datetime a record was last updated needs to be presented in their local time, in the browser. I have never been intimate with web apps that accomodate user profile time settings, but have used many. They let you set your local timezone. Else the app presents UTC. So, something internal to that app (joomla, drupal etc) must be optionally adjusting datetimes presented to the users. That's what I was suggesting; I think your reqly and the one prior to it suggest that these things are somehow 'handled'. Tom Lane seemed to point to a param to use with tz and I'm trying to figure that out. As far as I can see, my original premis is correct, that I need to tweak the stored datetime when returned for viewing in a browser to adjust for any user's tz setting...I'd be wrong if browsers automatically adjust any datetime for the requesting pc's tz setting. I don't think they do, else why would web apps like joomla and drupal adjust the datetime presented to the end user according to whatever they set at their tz? Your explanation of now() and localtime() is good...but localtime() to postgres acting as a web app db, wouldn't it just return the local time as far as the server is concerned? Martijn van Oosterhout wrote: > > On Mon, Sep 10, 2007 at 07:46:17AM -0700, novnov wrote: >> But 'apps all know to adjust for TZ'; really? In this case I'm creating >> the >> app, I can't imagine that it will automatically know to adjust for TZ? >> The >> postgres function now() would output now as far as the server is >> concerned >> (per it's local time setting I suppose), but very surprising is there is >> enough magic to report to user time as adjusted to their time zone as >> recorded in the db? > > Look up the definition of the time() function. In UNIX time is measured > in seconds since 1 Jan 1970 UTC. This is a number that is obvisouly the > same no matter where you are in the world. The C library has a function > called localtime() that does all the magic of converting to whatever > you consider local time. > > The postgres function now() is the same: it returns a number of seconds. > Only when you actually go to display it does it do the localtime() > dance (internally) to make it look nice. That why you can set the > timezone to whatever you like and everything works the same. > > I can log into a number of servers in the world and I just have it > setup to export TZ="my timezone" and everything is in my timezone, no > matter where the machine is or whatever anyone else on the machine is > doing. It's not magic, just someone 30 years ago making the smart > choice. > > Have a nice day, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ >> From each according to his ability. To each according to his ability to >> litigate. > > > -- View this message in context: http://www.nabble.com/Time-Zone-design-issues-tf4411984.html#a12595616 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Martijn van Oosterhout escribió: > The postgres function now() is the same: it returns a number of seconds. > Only when you actually go to display it does it do the localtime() > dance (internally) to make it look nice. That why you can set the > timezone to whatever you like and everything works the same. FWIW, the equivalent to the TZ variable in Postgres is setting the TimeZone variable (either using SET at the start of the session, or via ALTER USER/SET, etc). -- Alvaro Herrera http://www.advogato.org/person/alvherre "E pur si muove" (Galileo Galilei)
On Mon, Sep 10, 2007 at 08:34:55AM -0700, novnov wrote: > Your explanation of now() and localtime() is good...but localtime() to > postgres acting as a web app db, wouldn't it just return the local time as > far as the server is concerned? It will return localtime relative to whatever you've configured the timezone to be. Have your webapp execute "set timezone = 'foo'" at the beginning of the session and everything will flow from there. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On 9/10/07, novnov <novnovice@gmail.com> wrote: > Your explanation of now() and localtime() is good...but localtime() to > postgres acting as a web app db, wouldn't it just return the local time as > far as the server is concerned? No, it would return it as the local time of the POSTGRESQL client. For instance, suppose that someone in NY, connects to a web app, which asks him what TZ he's in and he picks EST5EDT. The web app sets timezone='EST5EDT' and inserts a time of '2007-07-11 12:30:00'. The database now stores that as '2007-07-11 16:30:00+00' No matter WHERE the postgresql server is, that's what it has in it. Now, I connect from Chicago, and your web app ascertains my timezone as CST6CDT. When I ask for the same timestamp, I get '2007-07-11 11:30:00-05'. Now, if you don't set a timezone, then the database will use it's own. If your postgresql server is in california, then it might have a timezone of PST8PDT set. If your web app didn't tell it otherwise, then the time would be '2007-07-11 09:30:00-07'. Note that there are IP lookup tools you can use to determine, or at least make a wild guess at, someone's timezone. But you still need to let them pick one if you get it wrong. Then you can store that timezone in a user profile and set it everytime the user uses your web app.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 09/10/07 10:55, Scott Marlowe wrote: > On 9/10/07, novnov <novnovice@gmail.com> wrote: >> Your explanation of now() and localtime() is good...but localtime() to >> postgres acting as a web app db, wouldn't it just return the local time as >> far as the server is concerned? > > No, it would return it as the local time of the POSTGRESQL client. > > For instance, suppose that someone in NY, connects to a web app, which > asks him what TZ he's in and he picks EST5EDT. The web app sets > timezone='EST5EDT' and inserts a time of '2007-07-11 12:30:00'. > > The database now stores that as '2007-07-11 16:30:00+00' > > No matter WHERE the postgresql server is, that's what it has in it. > > Now, I connect from Chicago, and your web app ascertains my timezone > as CST6CDT. When I ask for the same timestamp, I get '2007-07-11 > 11:30:00-05'. > > Now, if you don't set a timezone, then the database will use it's own. > If your postgresql server is in california, then it might have a > timezone of PST8PDT set. If your web app didn't tell it otherwise, > then the time would be '2007-07-11 09:30:00-07'. > > Note that there are IP lookup tools you can use to determine, or at > least make a wild guess at, someone's timezone. But you still need to > let them pick one if you get it wrong. Then you can store that > timezone in a user profile and set it everytime the user uses your web > app. In both Windows *and* Unix "you" set your TZ when you install the system. There are system functions to inquire how you've set it. Browsers already report back a mountain of client data to the web server. I'd be stunned if FF, IE, Opera, Konq, etc don't already expose TZ, too. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG5W04S9HxQb37XmcRAhsrAKCr3WQZi0oyWSJsMxortjoExeaS1QCg5HbS G+fd0X7UvX9406A+Td2GYpw= =wk17 -----END PGP SIGNATURE-----
novnov wrote: > As far as I can see, my original premis is correct, that I need to tweak the > stored datetime when returned for viewing in a browser to adjust for any > user's tz setting...I'd be wrong if browsers automatically adjust any > datetime for the requesting pc's tz setting. I don't think they do, else why > would web apps like joomla and drupal adjust the datetime presented to the > end user according to whatever they set at their tz? > > Your explanation of now() and localtime() is good...but localtime() to > postgres acting as a web app db, wouldn't it just return the local time as > far as the server is concerned? To be precise, it's as far as the *client library* (e.g. DBD::Pg in Perl, or your pg_xxx functions in PHP) is concerned. => show timezone; TimeZone ---------- GB (1 row) => select now(); now ------------------------------- 2007-09-10 16:46:51.275526+01 (1 row) => SET timezone='EST'; SET => select now(); now ------------------------------- 2007-09-10 10:46:51.275526-05 (1 row) So you'll want to issue the correct "SET timezone" for each user, but after that it's all handled for you. If the application wasn't web-based you'd not need to keep re-connecting so you'd only have to do it once. -- Richard Huxton Archonet Ltd
I think I get the picture; this post is the closest to making sense to me (my lack of understanding is the issue, obviously). But: What's the postgresql client, in a web app? When you write "The web app sets timezone='EST5EDT' and inserts a time of '2007-07-11 12:30:00'." that's the black box that I'm asking about. There is no web app other than the one I am writing. So, I think it means I need to write code in my web app to handle the conversion of the postgres stored UTC into user-relevant time, with their local time zone etc. If that's so, what I'm asking about is one the webapp side and perhaps people here are not so used to dealing with that. I don't know. Just like one of the other (much appreciated) responders in this thread suggested, the user's browser is likely offering the user's tz info...but I am interested in seeing someone's mature system for returning the user's local time based their tz as stored in their profile. But since no one has agreed with my positing of that approach so far I'm not sure. I'll see what I can figure out with a bit of testing etc. Thanks everyone. Scott Marlowe-2 wrote: > > On 9/10/07, novnov <novnovice@gmail.com> wrote: >> Your explanation of now() and localtime() is good...but localtime() to >> postgres acting as a web app db, wouldn't it just return the local time >> as >> far as the server is concerned? > > No, it would return it as the local time of the POSTGRESQL client. > > For instance, suppose that someone in NY, connects to a web app, which > asks him what TZ he's in and he picks EST5EDT. The web app sets > timezone='EST5EDT' and inserts a time of '2007-07-11 12:30:00'. > > The database now stores that as '2007-07-11 16:30:00+00' > > No matter WHERE the postgresql server is, that's what it has in it. > > Now, I connect from Chicago, and your web app ascertains my timezone > as CST6CDT. When I ask for the same timestamp, I get '2007-07-11 > 11:30:00-05'. > > Now, if you don't set a timezone, then the database will use it's own. > If your postgresql server is in california, then it might have a > timezone of PST8PDT set. If your web app didn't tell it otherwise, > then the time would be '2007-07-11 09:30:00-07'. > > Note that there are IP lookup tools you can use to determine, or at > least make a wild guess at, someone's timezone. But you still need to > let them pick one if you get it wrong. Then you can store that > timezone in a user profile and set it everytime the user uses your web > app. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -- View this message in context: http://www.nabble.com/Time-Zone-design-issues-tf4411984.html#a12601031 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Mon, Sep 10, 2007 at 12:44:04PM -0700, novnov wrote: > What's the postgresql client, in a web app? Your webapp *is* the postgresql client. The client is whatever opens the connection to the server. > When you write "The web app sets timezone='EST5EDT' and inserts a time of > '2007-07-11 12:30:00'." that's the black box that I'm asking about. There is > no web app other than the one I am writing. So, I think it means I need to > write code in my web app to handle the conversion of the postgres stored UTC > into user-relevant time, with their local time zone etc. No, you really just send the query "set timezone ='EST5EDT'". You don't have to code anything special at all. Have you tried this? Just go into the psql prompt, add some data and switch the timezones around to see what happens. Your app doesn't need to know anything about the timezones at all... > If that's so, what > I'm asking about is one the webapp side and perhaps people here are not so > used to dealing with that. I don't know. Just like one of the other (much > appreciated) responders in this thread suggested, the user's browser is > likely offering the user's tz info...but I am interested in seeing someone's > mature system for returning the user's local time based their tz as stored > in their profile. The user's browser doesn't send the timezone (probably because there's no standard way of specifying it). However there is code on the web to give you a guess though, by using javascript to get time difference from UTC. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
novnov escribió: > > I think I get the picture; this post is the closest to making sense to me (my > lack of understanding is the issue, obviously). But: > > What's the postgresql client, in a web app? > > When you write "The web app sets timezone='EST5EDT' and inserts a time of > '2007-07-11 12:30:00'." that's the black box that I'm asking about. There is > no web app other than the one I am writing. So, I think it means I need to > write code in my web app to handle the conversion of the postgres stored UTC > into user-relevant time, with their local time zone etc. You are misunderstanding the "sets timezone=". What it means is that you call the following SQL command: SET timezone TO 'EST5EDT'; early in your webapp code. The value to use, you get from the user profile (stored in a table perhaps). > If that's so, what I'm asking about is one the webapp side and perhaps > people here are not so used to dealing with that. I don't know. Just > like one of the other (much appreciated) responders in this thread > suggested, the user's browser is likely offering the user's tz > info...but I am interested in seeing someone's mature system for > returning the user's local time based their tz as stored in their > profile. I wouldn't trust the browser's TZ, and you would need a way to override it. So storing it in a table seems the easiest way. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 09/10/07 15:21, Alvaro Herrera wrote: [snip] > > I wouldn't trust the browser's TZ, and you would need a way to > override it. Why? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG5asLS9HxQb37XmcRAoQqAKCoqdRr6cIOnIktIa8l2689isYtAQCbBK9z /O1wYFZrtlols1lrvo1Rw5Q= =WcJ6 -----END PGP SIGNATURE-----
Ron Johnson <ron.l.johnson@cox.net> writes: > On 09/10/07 15:21, Alvaro Herrera wrote: >> I wouldn't trust the browser's TZ, and you would need a way to >> override it. > Why? The browser may not know the setting, or may not tell it to you, or you might not be able to make any sense of what it says (timezone names are hardly standardized). This whole sub-thread actually is predicated on an assumption not in evidence, which is that there is any browser anywhere that will tell the http server timezone information. I'm quite sure no such thing is required by the http standard. regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 09/10/07 19:50, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: >> On 09/10/07 15:21, Alvaro Herrera wrote: >>> I wouldn't trust the browser's TZ, and you would need a way to >>> override it. > >> Why? > > The browser may not know the setting, or may not tell it to you, > or you might not be able to make any sense of what it says > (timezone names are hardly standardized). Well that's true. Except for numeric offsets. > This whole sub-thread actually is predicated on an assumption not > in evidence, which is that there is any browser anywhere that will > tell the http server timezone information. I'm quite sure no such > thing is required by the http standard. I'm really surprised. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG5ff8S9HxQb37XmcRAstvAJ4tnhHhv9SCWnrWGq8td5orPttrfgCg1c+t IyhqUpzNg6RDS3wkALx5mUc= =BtHs -----END PGP SIGNATURE-----
> > The browser may not know the setting, or may not tell it to you, > > or you might not be able to make any sense of what it says > > (timezone names are hardly standardized). > > Well that's true. Except for numeric offsets. Offsets aren't good enough due to changing DST rules. A current offset may not be the right one for a stamp from 6 hours ago, and the DST changeover point may be different for a stamp from a year ago.
"Ron Johnson" <ron.l.johnson@cox.net> writes: > On 09/10/07 19:50, Tom Lane wrote: > >> This whole sub-thread actually is predicated on an assumption not >> in evidence, which is that there is any browser anywhere that will >> tell the http server timezone information. I'm quite sure no such >> thing is required by the http standard. > > I'm really surprised. I think all you get is the localized language. If it's localized to a particular country then that might be good enough for a guess from some countries but there's not much you can do with en_US or ru_RU. I think most big commercial sites that decide they need this just buy access to one of the ip to geographic location services which are far from perfect but in my experience are plenty good enough to get a reasonable time zone. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Sep 11, 2007, at 2:48 AM, Gregory Stark wrote: > "Ron Johnson" <ron.l.johnson@cox.net> writes: > >> On 09/10/07 19:50, Tom Lane wrote: >> >>> This whole sub-thread actually is predicated on an assumption not >>> in evidence, which is that there is any browser anywhere that will >>> tell the http server timezone information. I'm quite sure no such >>> thing is required by the http standard. >> >> I'm really surprised. > > I think all you get is the localized language. If it's localized to a > particular country then that might be good enough for a guess from > some > countries but there's not much you can do with en_US or ru_RU. > > I think most big commercial sites that decide they need this just > buy access > to one of the ip to geographic location services which are far from > perfect > but in my experience are plenty good enough to get a reasonable > time zone. Or, more likely, use one of several approaches to either get the timezone from the browser or get the browsers view of localtime and do a little math on the server. Javascript, mostly. (Though, AIUI, if you're using Javascript the elegant trick is to send UTC qpoch time to the browser and have it do the rendering to the local timezone anyway). Cheers, Steve
Alvaro Herrera wrote: > novnov escribió: >> I think I get the picture; this post is the closest to making sense to me (my >> lack of understanding is the issue, obviously). But: >> >> What's the postgresql client, in a web app? >> >> When you write "The web app sets timezone='EST5EDT' and inserts a time of >> '2007-07-11 12:30:00'." that's the black box that I'm asking about. There is >> no web app other than the one I am writing. So, I think it means I need to >> write code in my web app to handle the conversion of the postgres stored UTC >> into user-relevant time, with their local time zone etc. > > You are misunderstanding the "sets timezone=". What it means is that > you call the following SQL command: > SET timezone TO 'EST5EDT'; > early in your webapp code. The value to use, you get from the user > profile (stored in a table perhaps). Sorry for the ignorance...but what defines a session in this context in say..perl Would it be something like $dbh = DBI->connect(); do your set timezone stuff here do your insert $rc = $dbh->disconnect; > >> If that's so, what I'm asking about is one the webapp side and perhaps >> people here are not so used to dealing with that. I don't know. Just >> like one of the other (much appreciated) responders in this thread >> suggested, the user's browser is likely offering the user's tz >> info...but I am interested in seeing someone's mature system for >> returning the user's local time based their tz as stored in their >> profile. > > I wouldn't trust the browser's TZ, and you would need a way to override > it. So storing it in a table seems the easiest way. > -- Troy Rasiah