4

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?

f3r3nc
  • 620
  • 7
  • 15

3 Answers3

4

Just a wild guess, but maybe you're casting the strings to an integer in three of the cases?

20100402103000 - 20100402103100 = -100

20100402103000 - 20100402103001 = -1

20100402103000 - 20100402102959 = 41

The other case does the conversion properly.

John
  • 15,990
  • 10
  • 70
  • 110
1

The correct function to use is UNIX_TIMESTAMP().

TIMESTAMP() returns a date(time), in the format '2003-12-31 00:00:00'.

Also, there is nothing wrong with using

SELECT TIMESTAMPDIFF(SECOND,NOW(),TIMESTAMP("2010-04-02 19:29:59"));
Joel L
  • 3,031
  • 1
  • 20
  • 33
  • the problem popped up subtracting a timestamp field from now(). would the solution be like: UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(mytimestamp)? – f3r3nc Apr 02 '10 at 16:19
  • SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP('2010-04-02 19:26:00') Produces the correct results (for me, 30 seconds) – Joel L Apr 02 '10 at 16:26
  • yeah well, i guess it's just better using the built in converter as the representation might change over time or it might run over in 2038 :) thanks tho – f3r3nc Apr 02 '10 at 16:32
0

I tried the following and received NULL

SELECT TIMESTAMPDIFF(SECOND, TIMESTAMP("stopTime"), TIMESTAMP("startTime")) FROM 
paktime WHERE fs = 1 AND employ LIKE "Mike Fowler"
PaulMc
  • 109
  • 11