Re: Time Zone design issues

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