| Previous · Next | |
| User | Message |
|
martink
|
Date: 12/29/2009 10:50 am · Subject: database timezone · Rating: 0
Hi,I ran into a bug where the dateOfPurchase field of transaction and the lastUpdated of transactionItems differ by an hour. I traced this back to the fact that dateOfPurchase is pouplated with the NOW() mysql function while the lastUpdated field get its value from WebGUI::DateTime->new($session,time())->toDatabase. The latter will alwys be in UTC but the NOW() function uses the timezone of the database, which in my case is UTC+1. At first glance, the solution seems to be to replace the NOW() call with the WebGUI::DateTime version, but then again, the db still inserts those date as if they are in the db's time zone, which is wrong. However storing data in anything else than UTC seems like a bad idea as well so NOW() is a wrong option as well. So myquestion is, what's the pollicy in this case? Methinks webgui should set the session TZ for the db when opening the connection, by issuing a 'SET time_zone = timezone;' command after connecting. After that dates can be safely inserted in UTC time zone. Martin*|/||/|* |
| Back to Top |
Rate [ | ]
|
|
martink
|
Date: 1/7/2010 3:41 am · Subject: Re: database timezone · Rating: 0
Any ideas on this?Martin martin@oqapi.nl wrote: > martink wrote: > > Hi, > > I ran into a bug where the dateOfPurchase field of transaction and the > lastUpdated of transactionItems differ by an hour. I traced this back to > the fact that dateOfPurchase is pouplated with the NOW() mysql function > while the lastUpdated field get its value from > WebGUI::DateTime->new($session,time())->toDatabase. The latter will > alwys be in UTC but the NOW() function uses the timezone of the > database, which in my case is UTC+1. > > At first glance, the solution seems to be to replace the NOW() call with > the WebGUI::DateTime version, but then again, the db still inserts those > date as if they are in the db's time zone, which is wrong. However > storing data in anything else than UTC seems like a bad idea as well so > NOW() is a wrong option as well. > > So myquestion is, what's the pollicy in this case? Methinks webgui > should set the session TZ for the db when opening the connection, by > issuing a 'SET time_zone = timezone;' command after connecting. After > that dates can be safely inserted in UTC time zone. > > > Martin*|/||/|* > > > http://www.webgui.org/forums/dev/database-timezone > ------------------------------------------------------------------------ > > > > |
| Back to Top |
Rate [ | ]
|
