difference between now and sysdate

[pradeepjain]

hi,
I got this definitions from mysql official site

now() - returns the current date and time.

SYSDATE() returns the time at which it executes

This differs from the behavior for NOW(), which 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.)

i did not get the actual meaning and when to use sysdate instead of now?

[Atli]

Hey.

Imagine having a stored procedure that inserts a billion rows into a table. Each row is supposed to have an identical time-stamp. The problem is that inserting a billion rows would take a lot of time.

If you use the SYSDATE() function, it would use the time when each individual INERT statement is executed, which means the rows would not have an identical time-stamp, because each statement is not executed at the same time.

To fix that, you can use the NOW() function. Instead of using the exact time when it is executed, it uses the time when the stored procedure was started, so the rows would all have the same time-stamp.

The example in the SYSDATE() manual entry explains this pretty well:

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 |
+---------------------+----------+---------------------+

In the top query, using the NOW() function, both return the same time even though they are executed two seconds a part. - In the bottom query, however, using the SYSDATE() function, they return the exact time when they are executed, two seconds a part.