|
From: Peter L. <pal...@gm...> - 2011-09-20 05:45:19
|
I have been wrestling with why some of the timestamps in my database are
14 or 15 hours out of whack. Specifically the end_time is wrong. If I
clockin/out at 10:00-11:00 am, the timestamps in the db are start_time
of 10am and end_time of 2am the next day. Duration shows as 60 mins. So
I think what is happening is that function fixStartEndDuration and
fix_entry_endstamp are changing the end times.
There is this comment at the start of function fixStartEndDuration
//Due to a bug in mysql with converting to unix timestamp from the
string,
//we are going to use php's strtotime to make the timestamp
from the string.
//the problem has something to do with timezones.
Can anyone shed some light on what this bug might have been? Was it a
mysql 4.x bug? The url
http://jokke.dk/blog/2007/07/timezones_in_mysql_and_php mentioned in the
same function talks about handling timezones in php. It appears to me
that this web site may have changed since the url was pasted into the
function as I remember there were a few added comments then which are no
longer there. So any description of what bug there was, is no longer there.
This problem is most likely influenced by the system timezone, which
mysql uses, and whatever timezone is given to php. But I haven't
investigated that on my system yet.
In a future release i would like to change the way dates/times are
stored in the database. I think the database needs to keep time in UTC
only. Now we could change the mysql datetime fields to timestamp. Values
for |TIMESTAMP| <http://dev.mysql.com/doc/refman/5.5/en/datetime.html>
columns are converted from the current time zone to UTC for storage, and
from UTC to the current time zone for retrieval. datetime fields store
time in local time with no conversion i.e. set by the server's timezone.
See here for a discussion
http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/ with
some suggestions in this blog that datetime fields are better for
performance, possibly because the database does not have to do date
conversions.
There is also a short howto /user experience on converting your database
to using UTC:
http://stackoverflow.com/questions/2982897/php-mysql-time-zone-migration
Anyway, the reason I want to convert to UTC in the database is to remove
the probable timezone problems that fixStartEndDuration seems to be
trying to correct. The more important reason however is to support
multiple timezones. Since we have made a major effort to
internationalise the language in the web pages, we should also manage
the timestamps better. Being able to manage multiple timezones would
mean allowing each user to save and display his times in his own
timezone. The application then becomes a useful application for
international use, where users are in many different timezones.
Peter
|