Re: Time Zone design issues

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(at)gmail(dot)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.