WHAT TIME IS IT?
This post started with a simple question: “Does the function NOW() get executed every time a row is examined?” According to the manual, “Functions that return the current date or time each are evaluated only once per query …. multiple references to a function such as NOW() … produce the same result. …. (However,) as of MySQL 5.0.12, SYSDATE() returns the time (the row is) executes. “
- CURDATE() returns the current date.
- CURTIME() returns the current time.
- UTC_DATE() returns the current UTC date.
- UTC_TIME() returns the current UTC time.
- NOW() return the current date and time.
- UTC_TIMESTAMP() returns the current UTC date and time.
- SYSDATE() returns the date and time at which the function executes.
- LOCALTIME(), LOCALTIME(), LOCALTIMESTAMP(), LOCALTIMESTAMP() are synonyms for NOW().
All these functions return some value of a date / time. For example, WEEK() returns the week number for date.
Here is an example of a common query.
SELECT stn, temp, lat, lon WHERE valid_time > NOW() – INTERVAL 1 HOUR;
This is the right way to make this query because NOW() is only executed once. If SYSDATE() was used, each row selected would call SYSDATE().
So, if NOW() is only executed once and SYSDATE() is executed every time, we should see this difference if we BENCHMARK these functions.
mysql> SELECT BENCHMARK( 10000000, NOW() ); +------------------------------+ | BENCHMARK( 10000000, NOW() ) | +------------------------------+ | 0 | +------------------------------+ 1 row in set (0.07 sec) +----------------------------------+ | BENCHMARK( 10000000, SYSDATE() ) | +----------------------------------+ | 0 | +----------------------------------+ 1 row in set (5.49 sec)
And it seems to be true. But what about real SQL statements. Will this hold up? Here is a simple table.
CREATE TABLE `TableName1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
I used this SQL statement to put records in the table. It adds a new records with a new date/time with each query. Like:
INSERT INTO `stage`.`TableName1`(`id`,`dtime`) VALUES ( NULL,NOW())
What if we then query the table every second. Will NOW() be processed as a part of the results? I used the SLEEP() function to delay each record.
SELECT id, dtime, (SELECT NOW()) FROM TableName1 WHERE NOT SLEEP(1);
This query took ten seconds to run for a table with ten rows and returns these results.
id dtime (SELECT NOW()) 1 2010-08-06 15:55:57 2010-08-06 16:05:06 2 2010-08-06 15:56:12 2010-08-06 16:05:06 3 2010-08-06 15:56:16 2010-08-06 16:05:06 4 2010-08-06 15:56:20 2010-08-06 16:05:06 5 2010-08-06 15:56:30 2010-08-06 16:05:06 6 2010-08-06 16:48:23 2010-08-06 16:05:06 7 2010-08-06 16:48:26 2010-08-06 16:05:06 8 2010-08-06 16:48:27 2010-08-06 16:05:06 9 2010-08-06 16:48:28 2010-08-06 16:05:06 10 2010-08-06 16:48:29 2010-08-06 16:05:06
As the documentation states, the NOW() is NOT processes every time.
What if NOW() is a part of a JOIN?
SELECT t.id, t.dtime, d.n FROM TableName1 AS t, (SELECT 1, NOW() AS n) AS d WHERE NOT SLEEP(1);
Again, this SELECT takes ten seconds to run for a table with ten rows.
id dtime n 1 2010-08-06 15:55:57 2010-08-06 17:09:00 2 2010-08-06 15:56:12 2010-08-06 17:09:00 3 2010-08-06 15:56:16 2010-08-06 17:09:00 4 2010-08-06 15:56:20 2010-08-06 17:09:00 5 2010-08-06 15:56:30 2010-08-06 17:09:00 6 2010-08-06 16:48:23 2010-08-06 17:09:00 7 2010-08-06 16:48:26 2010-08-06 17:09:00 8 2010-08-06 16:48:27 2010-08-06 17:09:00 9 2010-08-06 16:48:28 2010-08-06 17:09:00 10 2010-08-06 16:48:29 2010-08-06 17:09:00
And again the NOW() function is only executed once.
The WHERE clause is harder to test. I inserted a row in the table only seconds apart. I then quickly ran the SELECT again to see if the NOW() matched the times in the table.
SELECT t.id, t.dtime FROM TableName1 AS t WHERE NOT SLEEP(1) AND dtime = (SELECT NOW() - INTERVAL 15 SECOND) ;
Here is the data table.
id dtime 1 2010-08-06 17:35:00 2 2010-08-06 17:35:00 3 2010-08-06 17:35:01 4 2010-08-06 17:35:03 5 2010-08-06 17:35:04 6 2010-08-06 17:35:04 7 2010-08-06 17:35:04 8 2010-08-06 17:35:05 9 2010-08-06 17:35:05 10 2010-08-06 17:35:05 11 2010-08-06 17:35:06 12 2010-08-06 17:35:06 13 2010-08-06 17:35:06 14 2010-08-06 17:35:07 15 2010-08-06 17:35:07 16 2010-08-06 17:35:07 17 2010-08-06 17:35:08 18 2010-08-06 17:35:08 19 2010-08-06 17:35:08 20 2010-08-06 17:35:09 21 2010-08-06 17:35:09 22 2010-08-06 17:35:09 23 2010-08-06 17:35:09 24 2010-08-06 17:35:10 25 2010-08-06 17:35:10
The select statement above only took three seconds to run and returned three rows all the same.
id dtime 11 2010-08-06 17:35:06 12 2010-08-06 17:35:06 13 2010-08-06 17:35:06
MySQL is evaluating dtime = (SELECT NOW() – INTERVAL 15 SECOND) before the SLEEP(1). MySQL only ran the SLEEP(1) for each record that matched. I reversed the two parts of the AND and the order of the didn’t matter.
Next I tried these functions with SYSDATE(). I found it is executed every time it is evaluated. This SELECT took 17 seconds and return a different time for each.
SELECT id, dtime, (SELECT SYSDATE()) FROM TableName1 WHERE NOT SLEEP(1);
id dtime (SELECT SYSDATE()) 1 2010-08-06 18:28:42 2010-08-06 19:00:57 2 2010-08-06 18:28:42 2010-08-06 19:00:58 3 2010-08-06 18:28:42 2010-08-06 19:00:59 4 2010-08-06 18:28:43 2010-08-06 19:01:00 5 2010-08-06 18:28:43 2010-08-06 19:01:01 6 2010-08-06 18:28:43 2010-08-06 19:01:02 7 2010-08-06 18:28:43 2010-08-06 19:01:03 8 2010-08-06 18:28:44 2010-08-06 19:01:04 9 2010-08-06 18:28:44 2010-08-06 19:01:05 10 2010-08-06 18:28:44 2010-08-06 19:01:06 11 2010-08-06 18:28:44 2010-08-06 19:01:07 12 2010-08-06 18:28:45 2010-08-06 19:01:08 13 2010-08-06 18:28:45 2010-08-06 19:01:09 14 2010-08-06 18:28:45 2010-08-06 19:01:10 15 2010-08-06 18:28:45 2010-08-06 19:01:11 16 2010-08-06 18:28:46 2010-08-06 19:01:12 17 2010-08-06 18:28:46 2010-08-06 19:01:13
The SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().
CACHING
MySQL doesn’t cache nondeterministic functions like NOW() and SYSDATE(). If your application processes the same data base queries within the same second or if you need the two queries to return the same results, you will need to turn NOW() into a string.
SELECT stn, temp, lat, lon WHERE valid_time > ‘2010-05-06 23:20:00’ – INTERVAL 1 HOUR;
Even better, do the math in your program. Here are some benchmarks show this outcome.
SELECT stn, temp, lat, lon WHERE valid_time > ‘2010-05-06 22:20:00’;
+------------------------------------------------------------------------+ | BENCHMARK( 10000000, NOW() - INTERVAL 1 HOUR = '2011-01-27 19:00:00') | +------------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------------+ 1 row in set (0.36 sec) mysql> SELECT BENCHMARK( 10000000, SYSDATE() - INTERVAL 1 HOUR = '2011-01-27 19:00:00'); +----------------------------------------------------------------------------+ | BENCHMARK( 10000000, SYSDATE() - INTERVAL 1 HOUR = '2011-01-27 19:00:00') | +----------------------------------------------------------------------------+ | 0 | +----------------------------------------------------------------------------+ 1 row in set (2.42 sec) mysql> SELECT BENCHMARK( 10000000, '2010-05-06 23:20:00' - INTERVAL 1 HOUR = '2011-01-27 19:00:00'); +----------------------------------------------------------------------------------------+ | BENCHMARK( 10000000, '2010-05-06 23:20:00' - INTERVAL 1 HOUR = '2011-01-27 19:00:00') | +----------------------------------------------------------------------------------------+ | 0 | +----------------------------------------------------------------------------------------+ 1 row in set (3.98 sec) mysql> SELECT BENCHMARK( 10000000, '2010-05-06 23:20:00' = '2011-01-27 19:00:00'); +---------------------------------------------------------------------+ | BENCHMARK( 10000000, '2010-05-06 23:20:00' = '2011-01-27 19:00:00') | +---------------------------------------------------------------------+ | 0 | +---------------------------------------------------------------------+ 1 row in set (0.22 sec)
REFERENCE:
http://dev.mysql.com/doc/refman/5.0/en/functions.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
Tweet
LinkedIn
Grennan.com
Twitter
The NOW() and SYSDATE() difference becomes important inside of stored procedures, functions and triggers. NOW() is executed once at the start of the stored procedure and its value re-used throughout, SYSDATE() is executed at each reference.
Some interesting portability issues spring up around these two functions also. In Oracle and other databases NOW() doesn’t exist and SYSDATE() provides the NOW() functionality. To address this mysql includes the –sysdate-is-now option to alias the two functions.
http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_sysdate-is-now
This becomes problematic inside of stored procedures for applications that are ported from other RDBMS’s
See: http://bugs.mysql.com/bug.php?id=30961
Link | January 27th, 2011 at 3:37 pm
Tweets that mention It’s about Time. | MySQL Fanboy -- Topsy.com wrote:
[...] This post was mentioned on Twitter by planetmysql and Zuissi, Le Krolique. Le Krolique said: It's about Time. – MySQL Fanboy http://t.co/0pkHKvy via @MySQLFanBoy [...]
Link | January 27th, 2011 at 4:25 pm
The timestamp column is cool but it would be nice to have a utc_timestamp option. For worldwide operations it just seems like it would be better to ensure everything is saved in UTC at the table definition level.
Link | January 27th, 2011 at 5:12 pm
Embed.ca » Video Marketing As A Business Building Weapon wrote:
[...] It's about Time. | MySQL Fanboy [...]
Link | January 27th, 2011 at 10:37 pm
Please note that SYSDATE() is not safe for statement based replication since 5.0.13 as it’s not affected by SET TIMESTAMP according the manual.
The pre 5.0.13 situation can be forced with the –sysdate-is-now option. This could also be a performance improvement and an interesting way to break an application.
Link | January 28th, 2011 at 6:36 am
I’ve found that using now() in a query prevents indexes from being used and assumed that it was re-evaluated for every row. If this is not the case, then why does it prevent indexes being used?
Link | February 19th, 2011 at 5:23 pm