Times and time zones

Hello all,

Back on this list after a long absence...

I'm working on a project where I have to allow users to input various dates
and times, including time zone, and then later allow them to view and if
necessary edit the times they have previously entered.

I've set the datatype for all the time/date columns to timestamp with time
zone, as it seems to be the only type that will store all the data I need,
but I was thrown a bit by the fact that PostgreSQL apparently then converts
all the times to GMT, setting the time zone to +00 accordingly. So if a user
enters July 20, 2005, 6:30 pm EDT (Eastern daylight savings time), it's
saved as 2005-07-20 22:30:00+00 rather than 2005-07-20 18:30:00-04.

Now, this is fine for comparing different times internally, but it's not so
good for outputting the times back to the user for them to view and edit.
I've now created a second column for each time which saves the time zone
they originally entered, but I'm having trouble converting the time back
from GMT to the original. I need to write a PHP function that will add or
subtract the appropriate number of hours according to the time zone, and
while this originally seemed simple, it doesn't seem to be working out
nearly as well as I thought.

There didn't seem to be a datatype specifically for timezones, so I stored
them as strings, but now when I attempt to add the timezone to the time in
PHP, using a simple function I wrote for that, it only returns the year, not
the date or time. And when I try to modify the dates in the database that
were entered before I added the ability to specify time zones so that they
reflect the time zone they should have been entered with, in phpPgAdmin, I
get told that my input syntax is wrong no matter how I try to specify the
time - "04:00", "4 hours", pretty much anything.

Can anyone give me some advice on how to handle these two issues - modifying
the times in the database via SQL, and converting the GMT times back to
their original time zones in PHP? Or even just point me at a tutorial or
reference somewhere about it? I've read through the date/time functions
section of the PHP manual and the date/time datatypes section of the
PostgreSQL manual over and over again, and that hasn't helped. Everything
I've been able to find on the subject seems to assume you want all your data
to be in one time zone and focusses on localization for your particular
zone, not on handling data submitted by international users in a wide
variety of time zones...

Thanks,

Lynna

--
Spider Silk Design - http://www.spidersilk.net
509 St Clair W Box 73576, Toronto ON Canada M6C 1C0
Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 416.873.9289