Why not to use NOW() in MySQL
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).
Code flow
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.
UNLOCK TABLES;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. :)
Written by Scott Horsley
Related protips
7 Responses
Isn't date('Y-m-d H:i.s') equivalent to date('Y-m-d H:i.s', time())?
And don't you mean 'Y-m-d H:i:s'?
@andyfleming,
Your right on both accounts.
1) date() with time() and without "should" produce the same result, however I prefer to use time() as date() accepts a timestamp to allow different result points in time. By leaving it in place, makes that very clear when reading the code.
2) MySQL will accept the : or . as valid.
Example:
mysql> create table foo (bar datetime);
mysql> insert into foo values ('2012-10-10 13:45.32');
mysql> select * from foo;
| 2012-10-01 13:45:32 |
Truncated for reading pleasure.
@andyfleming instead of manually define the date pattern, you may also use DateTime::ISO8601
While NOW() is replication-safe in general, you must use the same time zone on all instances, otherwise you'll get unexpected results. That's why I'd try to avoid them...
great article. the only time i use NOW() is mysql queries are in WHERE statements when trying to retrieve current data.
Good advice. I take this a step further and actually define a constant NOW in PHP at the beginning of the request, and in most cases refer to this constant instead of using time().
For situations where NOW() is a safe option we switched to using UTC_TIMESTAMP() and ensuring all dates are stored as UTC.
