keep timezone UTC

[mandra]

I have my.ini and php.ini set to UTC and would like times in Xataface displayed in UTC as well.
How can I disable the Xataface time conversion for users?

[mandra]

Just wanted to add: I've also changed the timezone in index.php to UTC (date_default_timezone_set('UTC');). The user system time is Europe/Berlin and as I said, I've set my.ini / php.ini / index.php to UTC. What do I have to do in order to store datetime data from xataface to mysql database without any time shift?

Thanks!

[shannah]

Xataface does a conversion to and from the php environment timezone and the mysql system timezone when it sets and gets dates in the database. If you have set your mysql timezone to UTC, then why is the mysql system tim Europe/Berlin? You would think it should be UTC?

I.e. it is my understanding that if you set both the MySQL time to UTC and the date_default_timezone_set('UTC'), then both PHP and MySQL should be working in UTC time, and no conversion should take place.

[mandra]

MySql is in UTC, it's just the OS (WinXP) that is running Europe/Berlin. I also thought that if I had everything running under UTC, then no time conversion should take place, but then maybe I have overlooked something. Although I have the mentionned settings, I have a 2 hour offset for datetimes entered during summertime and a 1 hour offset for values I have entered today (wintertime). The times in the database are correct, only the way they are displayed in Xataface is wrong.

Is there anything I could have overlooked? I've tried different settings in my Xataface index.php and saw that it is working, however setting "UTC" doesn't give me the desired result. I'm using XAMPP and in phpMyAdmin under Variables I read:
system time zone Westeurop
time zone UTC
could this be the problem? As I said, I have set UTC in my.ini. I there any other place I must set the time zone?

Thanks for your help!!!

[mandra]

although it wasn't the kind of solution I was looking for, I've just changed the OS time to UTC and it works, so it is obviously not an Xataface problem...

[shannah]

Thanks for bringing this up. Though you solved the problem outside of xataface, there could be a problem with xataface's handling of timezones. Currently it uses the convert_tz() mysql function to convert to and from system time as values are stored or retrieved. Unfortunately it seems that 'SYSTEM' time refers to the timezone of the host server and not necessarily the MySQL environment settings.

I think that perhaps we should change to just setting the timezone on the mysql connection.... but some testing will be required to ensure that nothing breaks. There are a number of apps built that depend on the current behavior of xataface with respect to timezones so I would want to be very careful before proceeding with a fix.

[jay]

Hi Steve.

I just noticed an issue related to the datetime conversion Xataface performs. A unit test of ours started failing today because the datetime rows were being saved with one more hour than expected -- we use Xataface to store the values in DB, but not to retrieve them.

This is the case: a datetime value like "Sat, 08 Oct 2011 02:45:00 GMT" was being stored and retrieved as "2011-10-08 04:45:00" using the GMT+2 TZ until last week, but after the DLST change (we're in GMT+1 TZ now) it's stored as "2011-10-08 05:45:00". I see that if we retrieve the row using a df_get_record* function, Xataface performs the right correction, but as I said, we're not using the API to read from DB (it's performed in a different machine in which Xataface is not even deployed).

I circumvented this behavior rewriting the df_utc_offset function so that it always returns 'SYSTEM'. We're not supporting different time zones, so it's more important that datetime values are stored as we set them in code.
Maybe you should consider adding a configuration property in Xataface that disables the datetime conversion at all.

Regards,
J.