CODEWITHSUNDEEP
mysql
user2112156
user2112156

Reputation: 53

MySQL timestamp math behaves differently after using unix_timestamp()

I'm trying to figure out what MySQL is doing during the math operation of timestamps.

Picture of resulting problem:

enter image description here

You'll see on the left I have two timestamps, start and end, and I need to find the duration from start to end so I just do this:

end - start

I was getting some really weird results. You can see for a duration of only 3 hours I was getting result back that indicated 2 to 3 times that amount.

When I convert to UTC first, the math works out fine.

Can anyone explain what SQL is doing with the timestamps on the left? I've always been under the impression that all timestamps are UTC under the hood, which is why things like min, max, less than, etc work without converting.

Thanks!

Code:

select  
    min(timestamp) start, 
    max(timestamp) end, 
    max(timestamp) - min(timestamp) start_to_end, 
    unix_timestamp(min(timestamp)) startUTC, 
    unix_timestamp(max(timestamp)) endUTC,
    unix_timestamp(max(timestamp)) - unix_timestamp(min(timestamp)) start_to_end_UTC
from post_snapshots group by permalink;

Upvotes: 3

Views: 1374

Answers (3)

John Flatness
John Flatness

Reputation: 33749

This isn't a DATETIME vs. TIMESTAMP or a time zone problem.

MySQL handles datetimes as operands to a subtraction (or other mathematical operation) by converting each value to a number, but it's not the number of seconds, its just the datetime digits crunched together. Take an example from your data:

2013-02-19 16:49:21 becomes 20130219164921

2013-02-19 19:07:31 becomes 20130219190731

The difference between those two numbers is... 25810, which is the value you're seeing as the result of your subtraction operation. That's not a result in seconds, as you noted. It really doesn't mean much useful at all.

In contrast, TIMESTAMPDIFF() (or pre-converting to Unix timestamps as you did) actually performs the difference using time-appropriate math if you're looking for the difference to be significant for much beyond sorting:

SELECT TIMESTAMPDIFF(SECOND, '2013-02-19 16:49:21', '2013-02-19 19:07:31')
>>> 8290

Upvotes: 1

mika
mika

Reputation: 1971

What happens is you cannot substract dates/datetimes in mysql. For all math operations, the mysql timestamp data type behaves like datetime data type. You could use instead

  select  
    TIMESTAMPDIFF(SECOND,min(timestamp),max(timestamp))
  from post_snapshots group by permalink;

Upvotes: 0

Capilé
Capilé

Reputation: 2078

These examples have nothing to do with timezone conversions -- when you subtract one date directly from the other, MySQL generates a integer from all existing date parts and then makes the math operations. For example, this query:

select now()+1;

returns (it was '2013-02-26 14:38:31' + 1):

+----------------+
| now()+1        |
+----------------+
| 20130226143832 |
+----------------+

So the difference between "2013-02-19 16:49:21" and "2013-02-19 19:07:31" turns out to be:

20130219190731 - 20130219164921 = 25810

The correct way for getting this subtraction is to either convert the dates to timestamps (like you did) or to use TIMESTAMPDIFF(SECOND, start_date, end_date), which would return 8290.

Upvotes: 1

Related Questions