Home > mysql, php > MySQL timestamp to Unix timestamp [PHP]

MySQL timestamp to Unix timestamp [PHP]


In MySQL, you have a field with timestamp type. Timestamp is used to track the elapsed time since ‘1970-01-01 00:00:00 UTC’. However, if you read it from the database, you get a date (2010-12-06 18:27:07) instead of its integer equivalent (1291678027). How to convert the date format to Unix timestamp?

Use case: I created a table with cached values. In each record, I have a “last_update” field of type timestamp. In my PHP script, I compare the current time with this last_update field. If the difference is more than X minutes, then I need to update the cache table. For calculating the time difference, first I convert the MySQL timestamp to Unix timestamp.


// from MySQL to UNIX timestamp
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;	// return type: int


$time_from_db = convert_datetime($tmp);
$time_now = time();
$time_diff = $time_now - $time_from_db;


I found this solution here.

Categories: mysql, php Tags: , ,
  1. dotancohenqqq
    July 16, 2014 at 07:08

    PHP actually has a function which works well for this:
    $time_from_db = strtotime($tmp);

    Alternatively, you could have the database perform the conversion as so:
    SELECT UNIX_TIMESTAMP(field) FROM someTable;

    The database way is preferable as I believe [citation needed] that MySQL stores the field as a timestamp internally anyway.

  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: