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

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().

Answer 2

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:

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

So, yes, there are specific scenarios where you might want to use SYSDATE() rather than NOW().

MySQL does provides several functions 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.