What SYSDATE is it?
I was looking into the history about why SYSDATE() and NOW() behave differently in MySQL, and it looks like in 4.0 and 4.1 they used to be the same. But as of MySQL 5.0, SYSDATE() was changed to emulate Oracle’s behavior, that is, it returns the time as of the function execution, not as of the statement start.
There are a number of bug reports related to this: 15101, 12480, 12481, and 12562.
I am not an Oracle expert. Does NOW() return a constant result within an Oracle query, like NOW() in MySQL does? Or is there no NOW() in Oracle, and you use SYSDATE() instead? Why is Oracle’s SYSDATE() functionality worth emulating? It looks to me like some people use SYSDATE() as a sort of cross-platform compatible NOW() function, but the saner behavior for me would be to have it be deterministic by default, for all the normal reasons that deterministic behavior is a Good Thing.
All of this is related to another topic I’ve been considering: should –sysdate-is-now be enabled in a “sane” default MySQL configuration, or is that just breaking something a lot of people rely on working the way it does? (It seems far more likely to me that it will unbreak things.)
Further Reading:
Heh,
Expect some queries involving this very issue on my coming talk on Percona Live, London.
Shlomi Noach
11 Oct 11 at 12:17 pm
I appreciate that there are two different functions for getting datetimes, though I almost always want the NOW() functionality in my queries.
I’ve only ever used SYSDATE() when testing something.
TehShrike
11 Oct 11 at 1:42 pm
NOW() is deterministic with replication because NOW() is an alias to CURRENT_TIMESTAMP(). SYSDATE() is not, and you can use that functionality to your advantage, much like pt-query-checksum take .
For example, I could do:
SELECT SYSDATE(), SLEEP(30), SYSDATE(); and SYSDATE would show the results. That’s a trivial example but it makes the point.
I’ve also seen it as a way to store a heartbeat with its corresponding lag – if you have a heartbeat table and insert NOW(), SYSDATE() into 2 time fields you can take the difference and see how far behind lag was at any given time.
SYSDATE() is the system date, and it uses the timezone of the server, so I’ve seen this be problematic, too (e.g. a machine in Eastern US time replicates to a machine in Pacific US time and uses SYSDATE() instead of CURRENT_TIMESTAMP() or NOW() and all the times are 3 hours different).
http://www.pythian.com/news/1295/does-anybody-really-know-what-time-it-is/
Times and timezones are VERY important. For instance, if you change the time on your server, some of your time values in MySQL change and others do not, depending on whether they are TIMESTAMP or DATETIME values.
Personally I’ve found that if folks are using SYSDATE() it’s because they’re coming from an Oracle environment, and they do plenty of other things to actually worry about (such as index every column and expect merge indexing to work the same as in Oracle).
Sheeri
11 Oct 11 at 2:34 pm
Baron, NOW() is not ANSI standard SQL, I do not believe it is in Oracle. CURRENT_TIMESTAMP() is the standard SQL term. (In fact, SYSDATE() isn’t in the standard either)…..that’s in the blog post, but I figured it might be interesting to folks even if they don’t click the link.
Sheeri
11 Oct 11 at 2:39 pm