Why not just tell PostgreSQL the timezone for the user and use
timestamp with timezone and let pgsql do the rest? I believe it's
SET TIMEZONE '-8'; for PST/PDT, etc.
Regards,
Gavin
On Jul 20, 2005, at 4:27 PM, Lynna Landstreet wrote:
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
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
Gavin M. Roy
800 Pound Gorilla
gmr@xxxxxxxx
