MySQL – Date and Time Related System Functions

MySQL supports lot of functions to get current date and time. The following are some of the system functions. Here is a quick look at these date and time related system functions.

There are two functions to get current date

SELECT curdate(),current_date();

There are two functions to get current time

SELECT curtime(),current_time();

There are two functions to get current date along with time

SELECT now(),current_timestamp();

SELECT utc_date();

SELECT utc_time();

SELECT utc_timestamp();

NOW Versus SYSDATE: A Difference Worth Knowing

Among the date and time related system functions, CURDATE and CURRENT_DATE are the same thing with two names. The same is true for CURTIME and CURRENT_TIME, and for NOW and CURRENT_TIMESTAMP. Pick one style and use it everywhere, so your code reads the same. There is one function that looks like a twin but behaves differently, and that is SYSDATE. NOW returns the time the statement started, so it gives the same value everywhere inside one statement. SYSDATE returns the exact time at the moment it runs. Run these two lines and watch the seconds.

SELECT NOW(), SLEEP(2), NOW();
SELECT SYSDATE(), SLEEP(2), SYSDATE();

In the first line both NOW values match. In the second line the two SYSDATE values are two seconds apart. The UTC functions are the other thing to remember. NOW and CURDATE follow the time zone of your session, while UTC_DATE, UTC_TIME and UTC_TIMESTAMP always return UTC. If your application has users in more than one time zone, store UTC and convert when you display. Finally, NOW, CURTIME and UTC_TIMESTAMP accept a precision for fractional seconds from 0 to 6, so NOW(3) gives you milliseconds, which is handy for logs. SYSDATE is also not safe for statement-based replication, so I stick with NOW unless I really need the live clock. Once you know these few rules, the whole family becomes easy to remember, and your reports show the right time for every user.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.