After many years of using MySQL (and coding) one of the things that seems to crop up constantly is why new team members under me always want to use NOW() in their MySQL queries and specifically why our (current) framework (We are primarily a PHP shop) doesn't have a method for creating a date field of NOW().
Well, here is an example of why that shouldn't be done, also included is my normal method of dealing with the problem. (Which is extremely trivial).
INSERT INTO foo (datetime_field) VALUES (NOW());
This seems at first glance to be harmless. Let's try that 100,000 times in a loop. Okay, that worked as expected also. Great!!! Not let's try something a little more complex. Start the previous command in a loop of 100,000 records and while that is running we will perform the following.
LOCK TABLES foo READ;
After the first loop is complete, you can then perform your normal unlock.
What you will find is that at the moment your LOCK came into play, your INSERT statement effectively queued on the server. What occurs after the lock is removed is that each statement is then processed by the MySQL server. However, the clock has moved forward at this point and all the NOW() entries will contain a time after the lock was removed.
Not quite what your statement was meant to do right?
Solution? This solution is specific to PHP but also works on any language that supports hand crafting SQL.
return date('Y-m-d H:i.s', time());
This is trivial but it means that the time sent to the database is the time that will be inserted. This also means that the time in the database is the time specified by the server doing the insertion. This might be important if both ends are in different timezones.
A better solution would possibly be to store the datetime as a timestamp (epoch) and then convert at each end. This makes it timezone irrelevant for everything.
Just a thought. :)