[SOLVED] mysql timestamp column displays as 01/01/1970 - why?

[Edward]

Hi,

 

I have a MySQL table which includes a TIMESTAMP column to record the time and date a comment was posted on a blog.

 

When I display the TIMESTAMP on the site, I am formatting it in the same way I would with date(), but it's not using the database value, it's using 1st Jan 1970. I know this date has some significance, but I'm not sure why it's showing up here.

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$date = $row['date'];
echo '<p>Date: '.date(l.', '.jS.' '.F.' '.Y, $date).'</p>';
}

 

Does anyone know what I've done wrong? Thank you.

 

[DarkWater]

Yeah, that's the Unix epoch.  The timestamp must be all 0's in the database.  How are you inserting it?

[Edward]

So far I've only created the rows in PHP MyAdmin and the timestamps have been created automatically. I've just checked the value in the table and it looks fine.

 

2008-07-01 13:24:45

[kenrbnsn]

The second parameter to the date() function needs to be a UNIX timestamp -- an integer, you can get this by using the strtotime() function:

<?php
while($row = mysql_fetch_assoc($result)) {
     $date = $row['date'];
     echo '<p>Date: ' . date('l,jS F Y', strtotime($date)).'</p>';
}
?>

 

Ken

[PFMaBiSmAd]

A TIMESTAMP column is in the format of a DATETIME data type yyyy-mm-dd hh:mm:ss. This is not the same as a Unix TIMESTAMP. To output this in any format you want use the mysql DATE_FORMAT() function in your query.

[Edward]

Thank you all!

 

I've used this which works:

$date = strtotime($row['date']);[code]

Thanks Ken. PFMaBiSmAd, I trust you have no objections to this method, i.e. using TIMESTAMP in my table and converting it with strtotime() before displaying it on my site? I'm not sure if you're saying I should use DATE_FORMAT over TIMESTAMP?

Thanks again!

[/code]

[revraz]

strtotime is just fine.

[DarkWater]

@Edward: DATE_FORMAT() is a function.  You use it in the SELECT query.