Re: Time Zone design issues

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.