Difference between NOW(), SYSDATE() & CURRENT_DATE() in MySQL

What difference between NOW() , SYSDATE() , CURRENT_DATE() in MySQL and where it can be used in real scenario .

I tried NOW(),SYSDATE(),Current_Date() when I insert data into a table and column datatype is TIMESTAMP all are given same date and time.

Answers


Current_date() will only give you the date. now() give you the datetime when the statement,procedure etc... started. sysdate() give you the current datetime. Look at the seconds after waiting 5 seconds between now()1 sysdate()1 with the following query (scroll to the right):

select now(),sysdate(),current_date(),sleep(5),now(),sysdate();

-- will give
-- now()    sysdate()   current_date()  sleep(5)    now()1  sysdate()1
-- 6/10/2014 2:50:04 AM 6/10/2014 2:50:04 AM    6/10/2014 12:00:00 AM   0   6/10/2014 2:50:04 AM 6/10/2014 2:50:09 AM

NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

Current_date returns the time stamp of the client while sysdate returns the time stamp of the server. If both server and the client are on the same machine, then, the result of both commands are the same. But in case that your sever is for example in USA and your clients are in China, then, these two functions return completely different results.

I don't know about thew now(), sorry :-)


CURRENT_DATE() is a synonym for many other similar functions all of which provide only the date. There is a subtle difference between NOW() and SYSDATE() which you can read up more on this official MySQL website page.


NOW() returns a constant time that indicate's the time at which the statement began to exicute whereas SYSDATE () returns the time at which it exicute... OR in other words NOW ()shows query exicution time and SYSDATE() shows self exicution time..


Need Your Help

What is the best way to make a simple cross platform GUI in C++?

c++ visual-studio user-interface cross-platform

I want to produce a desktop application with a very simple GUI (a background graphic, a cancel button and a progress bar).

Xcode 4.5 - CopyPNGFile persists. Need new ideas

ios xcode ios6 ios-simulator xcode4.5

I'm making an iPad app and it runs on the simulator, but fails when i try building it for the device.