[SOLVED] Using mysql timestamp with php

[Stealth549]

I wasn't sure where to post this either this section or the mysql section. Anyway I have a automated timestamp within mysql. Now when I use the date function I dont get the result that I was expecting. Getting the incorrect date.

 

this is the code that I am using and the results can be seen at the url below.

date("Y-m-d",$row['date'])

 

http://stealth549.com/dev/news/customer/php/includes/datetest.php

 

Thanks for any help.

[rarebit]

I don't use mysql timestamp, rather I use php's time() function which is unix time (seconds since jan 1st 1970, or something), however this is what mysql say's about it's timestamp, http://dev.mysql.com/doc/refman/5.0/en/timestamp.html, it seems that this is the format ''YYYY-MM-DD HH:MM:SS''

[GingerRobot]

The second parameter of the date() function is a timestamp. A timestamp is a number in seconds which is the time that has passed since the unix epoch (1/1/1970). Therefore, to change the the format of the date, you would first need to convert the date time field you have to a timestamp, either with the strtotime() function, or with the mysql UNIX_TIMESTAMP() function.

 

However, the mysql DATE_FORMAT() function will take a date time string as its value. This is probably your best option:

 

<?php
$sql = "SELECT DATE_FORMAT( `yourfield` , '%y %c %e' )  FROM `yourtable`";
$result = mysql_query($sql) or die(mysql_error());
?>

[Stealth549]

Thanks for your help, I got the results I was looking for using the time() function.