Thread: 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
At 07:27 PM 7/20/05, Lynna Landstreet wrote: >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 Why use php when sql will do the job? http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
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@ehpg.net
On 7/20/05 7:36 PM, Frank Bax <fbax@sympatico.ca> wrote: >> 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 > > Why use php when sql will do the job? > > http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html#FUNCTIO > NS-DATETIME-ZONECONVERT Aaagh! Can't believe I missed that! Thank you! So to use this, I'd just add it to my select statement when retrieving the data? As in "SELECT TIMESTAMP WITH TIME ZONE arrival_time AT TIME ZONE arrival_time_zone, [and various other fields] FROM [table] WHERE [condition]"? And for the time zone value, can it take all the input types listed for timezone in the datatypes section (http://www.postgresql.org/docs/7.4/interactive/datatype-datetime.html#DATAT YPE-TIMEZONE-TABLE), i.e. PST, -8:00, -800, -8, uniform, u? 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
On 7/20/05 7:36 PM, Gavin M. Roy <gmr@ehpg.net> wrote: > 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. That command works for an entire session, correct? Unfortunately, this is a travel-related site, so sometimes there are going to be arrival and departure times in different time zones, so I don't think that would work in this case. But it's likely to be useful in other applications, so thank you for the info. 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