Why does the minus operator give different result than the TIMESTAMPDIFF() function in mysql?
Posted
by f3r3nc
on Stack Overflow
See other posts from Stack Overflow
or by f3r3nc
Published on 2010-04-02T16:02:33Z
Indexed on
2010/04/02
16:13 UTC
Read the original article
Hit count: 527
Since TIMESTAMP in mysql is stored as a 32bit value representing the time interval from 1970-jan-1 0:00:00 in seconds, I assumed that using minus (-) operator on TIMESTAMP values would give the difference of these values in seconds. Actually not:
+---------------------------------------------------------------------+
| TIMESTAMP("2010-04-02 10:30:00") - TIMESTAMP("2010-04-02 10:29:59") |
+---------------------------------------------------------------------+
| 41.000000 |
+---------------------------------------------------------------------+
1 row in set (0.05 sec)
mysql> select timestampdiff(SECOND,TIMESTAMP("2010-04-02 10:30:00"),TIMESTAMP("2010-04-02 10:29:59"));
+-----------------------------------------------------------------------------------------+
| timestampdiff(SECOND,TIMESTAMP("2010-04-02 10:30:00"),TIMESTAMP("2010-04-02 10:29:59")) |
+-----------------------------------------------------------------------------------------+
| -1 |
+-----------------------------------------------------------------------------------------+
mysql> select TIMESTAMP("2010-04-02 10:30:00") - TIMESTAMP("2010-04-02 10:30:01") ;
+---------------------------------------------------------------------+
| TIMESTAMP("2010-04-02 10:30:00") - TIMESTAMP("2010-04-02 10:30:01") |
+---------------------------------------------------------------------+
| -1.000000 |
+---------------------------------------------------------------------+
+---------------------------------------------------------------------+
| TIMESTAMP("2010-04-02 10:30:00") - TIMESTAMP("2010-04-02 10:31:00") |
+---------------------------------------------------------------------+
| -100.000000 |
+---------------------------------------------------------------------+
It seems like one minute difference is 100 instead of 60.
Why is this?
© Stack Overflow or respective owner
