I fix bugs for a living. I found this doozy recently. Get out your sticky yellow pad and write this down:

Don’t do this:

select now() - max(my_timestamp) from my_table;

Go find a table with a TIMESTAMP field in it.

mysql> select my_timestamp from my_table limit 1;
my_timestamp: 2006-02-15 02:22:15
1 row in set (0.00 sec)

Looks nice. Now try:

mysql> select max(my_timestamp) from my_table;
max(my_timestamp): 2007-01-31 22:39:17
1 row in set (0.35 sec)

Now this:

mysql> select max(my_timestamp)+0 from my_table;
max(my_timestamp)+0: 2007
1 row in set (0.35 sec)

What happened? max(my_timestamp) was evaluated in a numeric context. The timestamp string was truncated at the first non-numeric character.

Something a bit different happens to now:

mysql> select now();
now(): 2007-02-06 22:47:25
1 row in set (0.00 sec)

mysql> select now()+0;
now()+0: 20070206224744
1 row in set (0.00 sec)

So the original exssion now() – max(my_timestamp) will yield

20070206224744 – 2007

which I doubt is what you had in mind.

If you need to calculate the difference in days, try:

to_days(now()) - max(to_days(my_timestamp))

For something a bit more fine-grained, try:

select (unix_timestamp(now())- unix_timestamp(max(stamp)))

This blog entry was posted on Friday, February 9th, 2007 at 10:51 am and is filed under Database, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.