16Jun/092
Insert into MySQL datetime column from PHP
You can use this simple function to convert a unix timestamp (like the one obtained from time()) to MySQL datetime format:
function mysql_datetime($timestamp = null) {
if(!isset($timestamp)) $timestamp = time();
return date('Y-m-d H:i:s', $timestamp);
}
if(!isset($timestamp)) $timestamp = time();
return date('Y-m-d H:i:s', $timestamp);
}
I'm using this instead of MySQL's NOW() function because my MySQL server isn't localized to my timezone, but my PHP is by usage of date_default_timezone_set.
Edit: And you can use this function to convert back the other way (you can do this directly in the SQL too, but just in case)
function datetime_to_unix($datetime) {
list($date, $time) = explode(' ', $datetime);
list($year, $month, $day) = explode('-', $date);
list($hour, $minute, $second) = explode(':', $time);
return mktime($hour, $minute, $second, $month, $day, $year);
}
list($date, $time) = explode(' ', $datetime);
list($year, $month, $day) = explode('-', $date);
list($hour, $minute, $second) = explode(':', $time);
return mktime($hour, $minute, $second, $month, $day, $year);
}
July 3rd, 2009 - 06:17
I usually just store dates as a mysql timestamp column since mysql timestamps are stored as unix time, which is relative to UTC.
http://en.wikipedia.org/wiki/Unix_time
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
You only have to worry that the timezone of the mysql server was correct when you saved a value. Reads automatically reflect the timezone it is read in.
July 3rd, 2009 - 14:48
Not sure if you can change the timezone of a mysql server on a shared host though, can you? The only problem with timestamps is that you can’t do date math on it, since as you said, it’s stored as unix time. Of course, it depends on your requirements; this may be a better solution. Thanks for the comment