Re: Dates - adding to unix 86400 seconds not equal nextday

["ioannes" <ioannes@xxxxxxxxxxxxxx>]

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" <simonr@xxxxxxxxxx>
To: "ioannes" <ioannes@xxxxxxxxxxxxxx>
Cc: <php-db@xxxxxxxxxxxxx>
Sent: Wednesday, September 01, 2004 3:16 PM
Subject: Re:  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