A problem with NOW() - date_registered

[penkomitev]

Hello,
Merry Christmas first. A lot of health, hapiness and luck during the new year. Now to the question. I have written one script getting time from MySQL as seconds and the the value is being converted as years, months, days, hours, minutes, seconds. In my opinion, the script is working fine, but the MySQL engine itselfs is making troubles. I attached screenshots to view what exactly the problem is.

This is for the field.



A few results from the table


The query, calculating NOW() - date_registered. I added few additional information for you to have better orientation.

[SQL]SELECT username, NOW(), date_registered, NOW() - date_registered AS diff FROM users[/SQL]



In my calculations these are 4 hours 16 minutes and 25 seconds. This is 4*3600+16*60+25 = 15385 != 41625 -> for the first row
on the second screenshot.

I do not want to reinstall engine but first to hear your opinion.

Best regards,
Penko Mitev

[Jordan]

I don't see any reason why that shouldn't work but you should use DATEDIFF() to subtract two dates:

SELECT username, NOW(), date_registered, DATEDIFF(NOW(), date_registered) AS diff FROM users

MySQL AB :: MySQL 5.0 Reference Manual :: 10.6 Date and Time Functions

[penkomitev]

Thank you for your reply. I tried several things including this one. In fact, TIMESTAMPDIFF is the best I found. It returns the result into seconds and I have a class called 'duration', which converts it into years to seconds(array elements) and I can use them easily. The problem with the method above was explained to me by one guy in one bulgarian forum. Here is a translation of his answer:


In the documentation of the function NOW is written that it returns the result in format YYYYMMDDHHMMSS.uuuuuu, so you are substracting dates:

20071225191625.000000 - 20071225150000.000000 = 41625
20071225191625.000000 - 20071224110227.000000 = 1081398
...

so there is nothing surprisely in the returned result. In fact, I understood it works only if the differences is 59 minutes maximum. When Hours are included it makes this problems.