PHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa

Question

im using php 5.4.6 and MySQL 5.5.29 and I get trouble by converting a UNIX TIMESTAMP to MYSQL DATETIME and vice versa. Mysql and php wun on the same local machine.

I have a simple mysql table

CREATE TABLE Entry(
id SERIAL PRIMARY KEY,
created DATETIME NOT NULL);

To insert data I use php PDO and mysql NOW(). This works fine, the correct datetime is stored in the database.

My plan is to work with unix timestamps on client side (php & mysql on server side).

So I would like to deliver unix timestamps to my client. Therefore I use MySql UNIX_TIMESTAMP() function to convert it directly in the query.

So a sample query looks like this:

SELECT created, UNIX_TIMESTAMP(created) AS TS FROM Entry

The result: created = 2013-02-14 20:47:35 TS = 1360871255

So now I want to do the other way, I pass the a UNIX Timestamp, and want to compare it with Entries in my Database. Unfortunetly Im not able to write a PHP script that works. I don't know why, but when I m passing the same timestamp (1360871255) to PHP I do not get 2013-02-14 20:47:35 with this method:

public static function toDateTime($unixTimestamp){
    return date("Y-m-d H:m:s", $unixTimestamp);
}

When I call toDateTime(1360871255) will return 2013-02-14 20:02:35 which is not the original DateTime.

I know, I dont need to format 1360871255 to a Y-m-d H:m:s to use it in MYSQL, but 1360871255 seems not to be the time that I expected (and MYSQL UNIX_TIMESTAMP has returned).

What I want to do is a simple query that shows me Entries that are older than a certain timestamp, something simple like this:

SELECT * FROM Entry WHERE created < 1360871255

but as I mentioned before, the query result is not the expected, because 1360871255 seems not to be the correct time.

I do not specify any special timezone for the mysql connection in php.

Any suggestions?

Answer 1

Your date format is wrong... i is for minute, not m (months).

return date("Y-m-d H:i:s", $unixTimestamp);

A few side notes:

• There's no need to re-assign, i.e. $unixTimestamp = $unixTimestamp;
• Since you're using PHP > 5.3. you may be interested in the new DateTime object.