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
