Monday, March 16, 2009

convert mysql DATETIME to timestamp ??

convert mysql DATETIME to timestamp ??

It would be nice to store my time stamp in my
mysql db as the YYYY-MM-DD hh:mm:ss format that
the msql uses because it is easily readable.

BUT is there a function that converts this into the timestamp format
so that I can use php to change the date with my script at another time ?

thanks if you know of one .

First Method:
In MySQL you can use a couple functions to convert back and forth between UNIX timestamps and MySQL datetimes: FROM_UNIXTIME() and UNIX_TIMESTAMP(). See for more info.

mysql> SELECT FROM_UNIXTIME(1196440219);
-> '2007-11-30 10:30:19'
mysql> SELECT FROM_UNIXTIME(1196440219) + 0;
-> 20071130103019.000000
-> '%Y %D %M %h:%i:%s %x');
-> '2007 30th November 10:30:59 2007'

Second Method:
* Function to turn a mysql datetime (YYYY-MM-DD HH:MM:SS) into a unix timestamp
* @param str
* The string to be formatted

function convert_datetime($str) {

list($date, $time) = explode(' ', $str);
list($year, $month, $day) = explode('-', $date);
list($hour, $minute, $second) = explode(':', $time);

$timestamp = mktime($hour, $minute, $second, $month, $day, $year);

return $timestamp;

No comments: