View Full Version : Datetime calculation gives weird result! Help!

05-19-2011, 04:07 PM
I am using MySql 5.1 on Debian Lenny

I noticed some weird behaviour in my code, so I investigated.

What it boils down to is this:

let's say there's a table called "abc" which contains a single column, called "whenStored" which is a DATETIME.

let's say there's one row in the table, and whenStored is set to the value 2011-05-18 13:22:13.

Consider the following query:

select now(), whenStored, now()-whenStored from abc;

I get the following three values:

2011-05-19 15:02:40
2011-05-18 13:22:13

Now it's that last value that has me completely confused ... given that now() here is nearly 26 hours after whenStored, then I would expect the value of now()-whenStored to be: a little less than 26*60*60, which is 93600.

But I'm getting 1018027, which is nearly 283 days!!!

WTF?! :confused::confused::confused:

As you can see I'm a little confused and frustrated ...

Any help gratefully received!

Thanks in advance.

05-20-2011, 01:11 PM
You can't just subtract or add two date/time values as you do in the case of normal numbers. There are built-in functions in mysql for this, like datediff() (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff) and timediff() (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff)

05-20-2011, 04:42 PM
Ah yes! Many thanks. I've managed to do it using the unix_timestamp function.