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
– deceze ♦, Commented Nov 8, 2010 at 3:35
I can't reproduce either, on 5.1.49, using the same value as TIMESTAMP or DATETIME. — OMG Ponies
– OMG Ponies, Commented Nov 8, 2010 at 3:46

Payload · Accepted Answer · 2010-11-08 08:35:31Z

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 8, 2010 at 8:35

Payload

1,7261 gold badge10 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

deceze Over a year ago

Although another poster in that ticket remarks to still have the same problems in 5.1.45. Very odd.

Payload Over a year ago

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.

JAL · Accepted Answer · 2010-11-18 00:59:47Z

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)

Collectives™ on Stack Overflow

PHP strtotime return different value of Mysql UNIX_TIMESTAMP

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related