Difference between SYSDATE(),NOW(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP() in MySQL? [closed]

Question

As per my knowledge,

SYSDATE(),
NOW(),
CURRENT_TIMESTAMP, and
CURRENT_TIMESTAMP() 

in MySQL are synonyms for one another. They all returns same value,then why MySQL provides all of them ? Are there different scenarios where I can use specific one of them ?

Answer 1

They aren't all synonymous.

SYSDATE() is different than NOW(), in a significant way. As a demonstration:

SELECT NOW(), SYSDATE(), SLEEP(5), NOW(), SYSDATE()

The output might look like this:

(playing where's waldo to find the difference, its the return from the last expression that is different, a second invocation of SYSDATE, after a delay of 5 seconds, returns a value that differs by 5 seconds)

2018-03-01 11:09:19
2018-03-01 11:09:19
0
2018-03-01 11:09:19
2018-03-01 11:09:24

(There are some scenarios where we want to use SYSDATE() rather than NOW())

MySQL provides a variety of expressions that can return the same result

DATE(NOW()) vs. CURRDATE()

I suspect that this is large part due to a desire to make transition from other Relational DBMS easier, by more closely matching (where possible and appropriate) the function names and syntax used by Oracle, SQL Server, and so on.

Answer 2

After reading http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_current-timestamp document, concluded that :

NOW() returns a constant time that indicates the time at which the statement began to execute. NOW() returns the time at which the function or triggering statement began to execute, but SYSDATE() returns the exact time at which it executes.

And CURRENT_TIMESTAMP, CURRENT_TIMESTAMP() are synonyms for NOW().