How to use Now() Function in MySQL | Explained with Examples

To obtain the current time and date of the system running MySQL server is necessary to perform certain actions, such as adding a new user in any web application. The “NOW()” function is a built-in MySQL function that returns the date and time value of the system on which the MySQL server is running.

This article will discuss the usage of the “NOW()” function in MySQL, along with its examples.

What is the “NOW()” Function in MySQL?

The “NOW()” function is a built-in function of MySQL to return the current time and date of the system that is running the MySQL server in a string format “YYYY-MM-DD HH:MM:SS”. The string format can convert to numeric “YYYYMMDDHHMMSS.uuuuuu” by adding “+ 0” after “NOW()”.

Let’s see different examples of the “NOW()” function in MySQL.

Example 1: MySQL “NOW()” Function With “SELECT” Statement

The “NOW()” function is used with a “SELECT” statement to return a result in a single row with a single column containing the current date and time. The command is provided below:

SELECT NOW();

The output displays the current date and time:

Example 2: MySQL “NOW()” Function With “DATE_FORMAT()” Function

The result of the MySQL “NOW()” function can be formatted by using the “DATE_FORMAT()” function to select the “NOW()” and format it using the format parameters of the “DATE_FORMAT()” function available on the official MySQL website. The “AS” clause is used to rename the column providing the result.

The command to format date in (Day of the week, Month name, Day of the month, year, date in AM/PM) way by typing the command:

SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y %r') AS "Current Date and Time";

The output is displaying the date in a formatted way:

Example 3: MySQL “NOW()” Function With “TIMEDIFF()” Function

The “TIMEDIFF” function can be used to find the difference of the current date and time obtained from the “NOW()” function with the specified date and time.

For example, to find the difference between the output of the “NOW()” function and the “2022-12-31 23:59:59” date and time with the column title as “Time Difference” using the command given below:

SELECT TIMEDIFF(NOW(), '2022-12-31 23:59:59') AS "Time Difference";

The output is showing the difference in the format “HH:MM:SS”:

Let’s see another example, to find the difference of the “NOW()” function result and the “2023-2-10 23:10:59” date and time with the column title as “Time Difference” by running:

SELECT TIMEDIFF(NOW(), '2023-2-10 23:10:59') AS "Time Difference";

Example 4: MySQL “NOW()” Function With “INSERT” Statement

Sometimes the user wants to add the value of current date and time in any table to keep track of any action so the MySQL “NOW()” function can be used as a value in the “INSERT” statement. For example, adding the value of “NOW()” in the “user” table along with the “id” value can be useful for recording the time when a new user was added to a database by running the query:

INSERT INTO user (id, time) VALUES (2, NOW());

select * from user;

The output will display the “user” table:

Example 5: MySQL “NOW()” Function With “UPDATE” Statement

The “UPDATE” statement aids in updating the already existing value with “NOW()” where the specified condition is matched defined by using the “WHERE” clause. For example, if you want to update the value of “time” with the value of “NOW()” where the “id” value is equal to “1”, by executing the query given below:

UPDATE user SET time = NOW() WHERE id = 1;

The output is clearly indicating that the value of “time” is successfully updated:

You are able to use the MySQL “NOW()” function.

Conclusion

The “NOW()” function is a built-in MySQL function to extract the value of the current date and time of the system running MySQL server in the string format. The output format can be changed to a numeric format as well. The “TIMEDIFF” and “DATE_FORMAT” can be used with “NOW()”. This post discussed the usage of the “NOW()” function in MySQL.