How to Get the Current Date and Time in MySQL

1. Get the Current Date and Time in MySQL
2. the NOW() Function
3. the CURRENT_TIMESTAMP() Function
4. the SYSDATE() Function

Today, we will learn the NOW(), CURRENT_TIMESTAMP() (also written as CURRENT_TIMESTAMP), and SYSDATE() to get the current date and time in MySQL. We will also see the 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 the NOW(), CURRENT_TIMESTAMP() also written as CURRENT_TIMESTAMP, SYSDATE(). We can easily use them in the INSERT statement as follows.

Example 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());

As 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 mentioned methods. A brief comparison of the NOW(), CURRENT_TIMESTAMP() and SYSDATE() is given below.

the NOW() Function

The date and time are returned by the method NOW() depending on what context we are using. For instance, the NOW() method returns date & time in string format as "YYYY-MM-DD HH-MM-SS" if the context is a string.

It returns in a numeric format as YYYYMMDDHHMMSS if the function uses a numeric context. It returns the constant time that shows the time on which this particular statement starts to execute.

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

the CURRENT_TIMESTAMP() Function

We can also write the CURRENT_TIMESTAMP() as CURRENT_TIMESTAMP. It returns the current date & time as "YYYY-MM-DD HH-MM-SS" or YYYYMMDDHHMMSS if the function uses the string context or numeric context, respectively.

The synonym of CURRENT_TIMESTAMP includes the NOW() and CURRENT_TIMESTAMP() functions. Execute the following query to see the same output.

SELECT NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP();

OUTPUT:

It is available in MySQL version 4.0 and above. It also outputs a constant time that represents the time on which this specific query starts to execute.

the SYSDATE() Function

Like the NOW() and CURRENT_TIMESTAMP() functions, it also returns the current date and time depending on the context used by this function. The context can be string or numeric.

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

After going through the MySQL documentation, we can say there is a subtle difference between SYSDATE() and NOW(). Remember, the NOW(), CURRENT_TIMESTAMP, and CURRENT_TIMESTAMP() returns 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 constant in the output given above except the result produced by the SYSDATE() function before and after the SLEEP() function. The NOW() method returns the constant time representing when the query starts to execute.

In a trigger or a stored procedure (also known as a stored function), the NOW() outputs the time at which the triggering statement or function starts to execute. While the SYSDATE() returns the exact time at which it runs (executes).