JIYIK CN >

In this article, we will learn about NOW(), CURRENT_TIMESTAMP() (also written as CURRENT_TIMESTAMP), and SYSDATE() to get the current date and time in MySQL. We will also see a comparison between these three functions.

Get the current date and time in MySQL

We have three methods to get the current date and time in MySQL. These methods include NOW(), CURRENT_TIMESTAMP() also written as CURRENT_TIMESTAMP, SYSDATE(). We can easily use them in INSERT statements as shown below.

Sample code:

# use NOW()
INSERT INTO student_attendance(ID, ATTENDANCE)
VALUES(1, NOW());

# use CURRENT_DATETIME
INSERT INTO student_attendance(ID, ATTENDANCE)
VALUES(1, CURRENT_DATETIME);

# use SYSDATE()
INSERT INTO student_attendance(ID, ATTENDANCE)
VALUES(1, SYSDATE());

Since we have multiple ways to insert the current date and time, it is important to understand the difference between them.

It will clarify when and where to use one of the above methods. A brief comparison of NOW(), CURRENT_TIMESTAMP(), and SYSDATE() is given below.

NOW() Function

The date and time returned by the method NOW() depends on the context we use it in. For example, if the context is a string, the NOW() method returns the date and time in string format like "YYYY-MM-DD HH-MM-SS".

If the function is used with a numeric context, it returns in the numeric format of YYYYMMDDHHMMSS. It returns a constant time showing the time when this particular statement started executing.

It is available and can be used in MySQL 4.0 and above.

CURRENT_TIMESTAMP() function

We can also write CURRENT_TIMESTAMP() as CURRENT_TIMESTAMP. It returns the current date and time as "YYYY-MM-DD HH-MM-SS" or YYYYMMDDHHMMSS if the function is used in a string context or a numeric context respectively.

Synonyms for CURRENT_TIMESTAMP include NOW() and CURRENT_TIMESTAMP() functions. Execute the following query to see the same output.

SELECT NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP();

Output:

NOW()    CURRENT_TIMESTAMP    CURRENT_TIMESTAMP()
2022-04-26 11:40:25    2022-04-26 11:40:25    2022-04-26 11:40:25

It is available in MySQL 4.0 and above. It also outputs a constant time, representing the time when this particular query started executing.

SYSDATE() function

Like the NOW()and CURRENT_TIMESTAMP()functions, it also returns the current date and time based on the context in which the function is used. The context can be a string or a number.

For string and numeric contexts, it returns "YYYY-MM-DD HH:MM:SS" and YYYYMMDDHHMMSS, respectively. The SYSDATE() function returns the exact time of query execution.

After reading the MySQL documentation, we can say that there is a subtle difference between SYSDATE() and NOW(). Remember that NOW(), CURRENT_TIMESTAMP, and CURRENT_TIMESTAMP() return the same results.

Execute the following query to notice the difference.

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

Output:

Everything remains the same in the output given above except the results produced by the function before and after the function. The NOW() method returns a constant representing the time when the query started executing SYSDATE().SLEEP()

In a trigger or stored procedure (also called stored function), NOW() outputs the time when the triggering statement or function began executing. SYSDATE() returns the exact time it ran (executed).