Daylight savings.

[jvinolas]

Hi,

I've got a problem with mysql timestamp and daylight savings (I think). While in phpmyadmin I got an entry as '2008-06-30 18:30:00', in php I got '2008-06-30 17:30:00'. This happens with dates on summer time only, so for other records (winter time) I get the same date in php.

How can we handle timestamp fields with xataface so I get the same date as in mysql?

Edit:
It seems that doing a "select ..." gets date with daylight savings and compared to strtotime($record->strval('DATA')) doesn't work well because it doesn't get date with daylight savings adjust.

Thanks.

[shannah]

Can you put together a minimal test case that shows the PHP code that demonstrates the disparity? You should be able to demonstrate this with 2 or 3 lines of code.

[jvinolas]

Hi,

This is the non-working code:

Code: Select all

      $sql = "SELECT sum(QUANTITAT) FROM contractes_detall WHERE contractes_detall.IDCONTRACTE=".$record->val('IDCONTRACTE')." AND  contractes_detall.DATA<='".date('Y-m-d G:i:s', strtotime($record->strval('DATA')))."'";
      $res = mysql_query($sql);
      $total = number_format(mysql_result($res,0),2,',','.');

And this the working code:

Code: Select all

      $sql = "SELECT DATA FROM contractes_detall WHERE ID=".$record->val('ID');
      $data = mysql_query($sql);
      $sql = "SELECT sum(QUANTITAT) FROM contractes_detall WHERE contractes_detall.IDCONTRACTE=".$record->val('IDCONTRACTE')." AND  contractes_detall.DATA<='".date('Y-m-d G:i:s', strtotime(mysql_result($data,0)))."'";
      $res = mysql_query($sql);
      $total = number_format(mysql_result($res,0),2,',','.');

So, it is getting different time (daylight savings applied in mysql select but not in $record->strval) when I use first code, where the data comparison is made against strtotime($record->strval('DATA')), while in the second code (the working one), I have to query mysql for current date (not using xataface api) and then use it inside my mysql query. This, of course, gets the same times from timestamp.

I'm pretty sure that my working code is not the ideal approach to what I'm trying to do. How can it be done using only xataface api?

Thanks.

[shannah]

I wonder if your mysql timezone is set up different than your system timezone. I wrote a blog post a long time ago on the issue of timezones in PHP/MySQL applications. This article is a window in the the development behind Xataface's timezone features.

http://www.sjhannah.com/blog/?p=113

When xataface loads records from the database, it uses the convert_tz() mysql function to automatically convert the time into the current environment's timezone.

You may want to check on the value of df_utc_offset() (function defined in dataface-public-api.php), to see what the environment timezone offset is.

-Steve