Formating dates is making each entry return todays date

[ajannick]

Hi All,

 

I'm having trouble formating dates returned from a database successfully, the code below does the formating but makes all the entries in the database share todays date and time:

 

<?php

while($row=mysql_fetch_array($sql))   

{

       

        $row['dateField'] = date("D M jS Y H:i");

       

echo"<tr>\n";

echo"<td background='graphics/cloud.jpg'><center><span class='blackA'>

              ".$row['dateField']."</span></center></td>\n";

echo"</tr>\n";

}

 

?>

 

This formats the date how I want it but then all the entries are retuned with the todays date and time values!

 

Any Ideas?

 

N.

 

 

 

 

[redarrow]

and the entry in the database is set as a time stamp is that correct.

[Wildbug]

$row['dateField'] = date("D M jS Y H:i");

 

That's because you're setting it to today's date and time at the above line of code.  Just put it in the echo line:

echo .... date("D M jS Y H:i",$row['dateField']) ....

 

 

See date() for syntax.  It needs to be a UNIX timestamp.  Also consider that you can format the date in your SQL query.

[ajannick]

Yes

[redarrow]

try this ok.

<?php
  while($row=mysql_fetch_array($sql))    
  {
        
       $dateField = date("D M jS Y H:i",$row['dateField']);
       
  echo"<tr>\n";
  echo"<td background='graphics/cloud.jpg'><center><span class='blackA'>
             $dateField</span></center></td>\n";
  echo"</tr>\n";
  }
  
?>

[ajannick]

when I put it in the echo line it comes up with all dates 1st JAN 1970 00:33?

[redarrow]

post your timestamp entry your testing ok.

[kenrbnsn]

The second parameter to the date() function must be an integer UNIX timestamp. You need to do:

<?php
$dateField = date("D M jS Y H:i",strtotime($row['dateField']));
?>

 

Ken

[ajannick]

SELECT "UNIX_TIMESTAMP(dateField) as timestamp ";

[ajannick]

This works just about!  The dates are now all correct but the times are all 7 hours infront?

[ajannick]

sorry 7 behind

[redarrow]

i think this is correct.

<?php
$H = date("H",strtotime($row['dateField'])-7);
$dateField = date("D M jS Y $H:i",strtotime($row['dateField']));
?>

[ajannick]

Strangely that knocks it 8 hours back?

[redarrow]

use -6 then ok

[ajannick]

result stays the same!

[Wildbug]

As I said earlier, you could also format the date/time directly in MySQL before retreiving the value and just print it like any other value.

 

// In your SQL query:
..., DATE_FORMAT(dateField,"%a %b %D %Y %H:%i") AS formatted_datefield, ...

// In your PHP code:
echo $row['formatted_datefield'];

 

(Are your date values stored as GMT in the database?)