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);
}

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);
}

2 thoughts on “Insert into MySQL datetime column from PHP

  1. 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 :)

Leave a Reply

Your email address will not be published. Required fields are marked *