PHP strtotime return different value of Mysql UNIX_TIMESTAMP

Question

I've searched post on stackoverflow,found some similar post.But I think this is a different one.

My PHP & Mysql server's timezone all set to "UTC".

In a table I use a timestamp field,value is "2010-11-08 02:54:15",I use sql like this:

SELECT id, 
       updated, 
       second( updated ) , 
       unix_timestamp( updated ) 
  FROM `transaction` 
 where id = 56

Got this:

id  updated              second  unix
--------------------------------------------
56  2010-11-08 02:54:15  15      1289184879

Then I use this in php:

echo strtotime("2010-11-08 02:54:15");

Got this:

1289184855

The different is 24 seconds.

And I check these timestamps on http://www.unixtimestamp.com/index.php The php result is the correct one. So the mysql unix_timestamp function has bug? Mysql version is: 5.1.41

Just for reference, `SELECT UNIX_TIMESTAMP('2010-11-08 02:54:15');` gives me 1289152455 on MySQL 5.1.37. — deceze, Nov 08 '10 at 03:35
I can't reproduce either, on 5.1.49, using the same value as TIMESTAMP or DATETIME. — OMG Ponies, Nov 08 '10 at 03:46

score 4 · Accepted Answer · answered Nov 08 '10 at 08:35

4

This is confirmed to be a bug that is fixed in 5.1.44.

See http://bugs.mysql.com/bug.php?id=51918 for details, the bug poster has found this issue exactly.

You will have no choice but to upgrade to avoid it by the looks of it.

answered Nov 08 '10 at 08:35

Payload

1,686
1
10
6

Although another poster in that ticket remarks to still have the [same problems in 5.1.45](http://bugs.mysql.com/bug.php?id=52624). Very odd. – deceze Nov 08 '10 at 08:46
Only with regards to the windows version in both 5.1.45 and 5.1.46, there is no further mention of this bug in versions past that, so 5.1.52 should be all good, but testing is the only way to be sure. – Payload Nov 09 '10 at 01:12

score 0 · Answer 2 · answered Nov 18 '10 at 00:59

I can't reproduce that in MySQL 5.1.41 on Linux. Perhaps this is Windows-only?

snip@ssnip:~$ mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 295
Server version: 5.1.41

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select unix_timestamp("2010-11-08 02:54:15");
+---------------------------------------+
| unix_timestamp("2010-11-08 02:54:15") |
+---------------------------------------+
|                            1289206455 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
maia@sloodle:~$ php -a
Interactive shell

php > echo strtotime("2010-11-08 02:54:15");
1289206455
php > exit
snip@ssnip:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 297
Server version: 5.1.41

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select from_unixtime(1289206455);
+---------------------------+
| from_unixtime(1289206455) |
+---------------------------+
| 2010-11-08 02:54:15       |                                                                                                          
+---------------------------+                                                                                                          
1 row in set (0.00 sec)

PHP strtotime return different value of Mysql UNIX_TIMESTAMP

2 Answers2

Linked