As Simon suggests, gmdate and gmmktime gives the right answer when:
- computing difference between two dates in a MySQL table eg 15/10/2004 and 15/11/2004 - should be 31
- adding a number of days to the start date to get the end date, eg 15/10/2004 + 31 - should be 15/11/2004
$start=gmmktime(0,0,0,10,15,2004);
$end=gmmktime(0,0,0,11,15,2004);
$gm_interval=($end-$start)/86400;
print("Interval computes $gm_interval"); //gives 31 right
$end_add=$start+31*86400; //add 31 days of seconds
$end_add_gm_interval=gmdate("d m Y",$end_add);
print("<br>Add 31 days computes $end_add_gm_interval"); //15 11 2004 right//whereas date() will fail on the DST issue
$start=mktime(0,0,0,10,15,2004);
$end=mktime(0,0,0,11,15,2004);
$date_interval=($end-$start)/86400;
print("<br>date()<br>");
print("Interval computes $date_interval"); //31.0416666667 wrong
$end_add=$start+31*86400; //add 31 days of seconds
$end_add_date_interval=date("d m Y",$end_add);
print("<br>Add 31 days computes $end_add_date_interval"); //14 11 2004 wrong
However, in the following queries, mktime gives 08 10 2004 whereas the date is 09 10 2004. gmmktime gives the right date. There is no 08 10 2004 in the table. This seems to be a different issue from daylight savings as it is not 31 October when the hour changes.
$query = "SELECT mydates FROM MyTable ORDER BY mydates";
$result=mysql_db_query($db,$query,$connection) or die("$query failed: ".mysql_error());
$tbl_dates=array(); $x=0;
while(list($a)=mysql_fetch_array($result)){
$temp_day=substr($a,8,2);//parse table date
$temp_mth=substr($a,5,2);
$temp_year=substr($a,0,4);
$tbl_dates[$x]=mktime(0,0,0,$temp_mth,$temp_day,$temp_year); //wrong - gmmktime gives right date
$x++;
}
Using the MySQL UNIX_TIMESTAMP() seemed to give the wrong date, 08 10 2004. I did not find the gmdate equivalent in mysql.com.
Conclusion: Using gmmktime and gmdate seems to work and is needed where intervals are to be calculated and number of days added to dates. Query: why the above SQL gave the wrong date with mktime when the date was 9 October....
Thanks,
John
----- Original Message ----- From: "Simon Rees" <[EMAIL PROTECTED]>
To: "ioannes" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, September 01, 2004 3:16 PM
Subject: Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
Isn't this due to how your operating system handles the switch from daylight saving time to GMT (or local equivilent)? Or is this handled by PHP? That could account for different experiences. Of course if your locale doesn't switch from DST on this date you won't see the issue either...
Using gmdate doesn't cause this anomaly.
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
