[Tsheetx-developers] Timezones, time adjustment and UTC

[Peter L. <pal...@gm...>]

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