intro
Let’s learn everything you need to know about the MySQL NOW function to retrieve the current date and time in your queries.
In programming, having a quick way to retrieve the current date and time is essential for recording events, handling time-sensitive data, and more. Not surprisingly, most programming languages provide a standard function for getting the current timestamp. As you can imagine, that need extends to databases as well. This is exactly what the MySQL NOW() function is all about!
In this article, you will learn what the NOW MySQL function is, how to use it to fetch the current timestamp, and see practical examples of its application in SQL queries.
Let’s dive in!
What Is MySQL NOW?
In MySQL, NOW is a function that returns the current date and time. More precisely, it returns the date and time at which the execution of the SQL statement began.
That particular behavior must be taken into account when using the MySQL NOW function in stored functions or SQL triggers. No matter how much time passes from one statement involving NOW to the next, the value returned by the MySQL date function will always be the same. The reason is that NOW returns the date and time the stored function or trigger began to execute.
How To Use NOW in MySQL
This is the syntax of the MySQL NOW function:
1
NOW()
As you can see, it does not accept any arguments. Just like any other SQL function, it can be used in stored functions, triggers, and SELECT, INSERT, UPDATE, and DELETE statements. Specifically, it can appear in WHERE, GROUP BY, HAVING, and ORDER BY clauses.
When used in string contexts, NOW returns the current date and time in the DATETIME/TIMESTAMP format 'YYYY-MM-DD HH:MM:SS':
1
SELECT NOW();
This would return a string like this:
1
2024-07-02 09:12:59
Find out more about date formats in our guide on SQL date data types.
When used in numeric contexts, NOW returns the current date and time in the format YYYYMMDDHHMMSS:
1
SELECT NOW() + 0;
This time, the result will be something like this:
1
20240702091259
Note: In both cases, the returned value is expressed in the session time zone.
Analyzing the SQL data type of a cell becomes even easier with DbVisualizer's object view feature:
Just click on a cell, and DbVisualizer will give you its types and format. Discover all DbVisualizer features!
Keep in mind that the value returned by the NOW function can be used with INTERVAL to add or remove time from the given datetime, as in the following example:
1
SELECT NOW() + INTERVAL 1 HOUR;
The new result will be:
1
2024-07-02 10:12:59
Learn more in our guide on SQL add to date operations.
Note that the NOW MySQL function has three synonyms:
MySQL NOW Function: Use Cases
The most common use cases of the datetime NOW MySQL function includes:
1
UPDATE tasks
2
SET scheduled_time = NOW() + INTERVAL 1 DAY
3
WHERE task_id = 15;
1
SELECT * FROM events
2
WHERE event_time BETWEEN NOW() AND NOW() + INTERVAL 2 WEEK;
1
INSERT INTO system_logs (event_type, event_details, log_time)
2
VALUES ('ERROR', 'Null pointer exception', NOW());
1
INSERT INTO orders (user_id, product_id, quantity, order_time)
2
VALUES (14, 101, 3, NOW());
1
SELECT username, TIMESTAMPDIFF(YEAR, birth_date, NOW()) AS age
2
FROM users;
1
DELETE FROM tokens
2
WHERE expiry_time < NOW();
1
DELETE FROM orders
2
WHERE order_date < NOW() - INTERVAL 1 YEAR;
Conclusion
In this guide, you learned what the MySQL NOW function is and how it works. You now know that NOW is a MySQL function that returns the date and time an SQL statement began to execute. Thanks to the examples shown here, you also saw when to use it in real-world scenarios.
NOW produces values with different formats depending on the context. As shown here, understanding the type and format of a value in a table cell is possible with just a click in a powerful SQL client like DbVisualizer. On top of that, this comprehensive database client supports several DBMS technologies, has advanced query optimization capabilities, and can generate ERD-type schemas with a single click. Try DbVisualizer for free!
FAQ
How to get the current date and time in MySQL?
If you want to get the date and time of when an SQL statement begins execution, use the NOW function. On the other hand, if you want to get the exact date and time—regardless of the execution time—you can use the SYSDATE function. This returns the exact time at which the function was executed.
What is the difference between the MySQL NOW and SYSDATE functions?
Both NOW and SYSDATE are MySQL date functions. NOW returns a constant time that indicates the time at which the statement began to execute, while SYSDATE returns the time at which the function executes. You can verify the different behavior of the two functions with the following query:
1
SELECT NOW() AS now_1, SLEEP(2), NOW() AS now_2,
2
SYSDATE() AS sysdate_1, SLEEP(2), SYSDATE() AS sysdate_2;
now_1 and now_2 will contain the same value, while sysdate_2 will contain a date with 2 seconds more than sysdate_1.
Is the MySQL NOW function part of the SQL standard?
The MySQL NOW function is not part of the SQL standard. Instead, the ANSI SQL standard specifies functions like CURRENT_TIMESTAMP, CURRENT_DATE, and CURRENT_TIME for similar purposes.
What is the MySQL timestamp NOW equivalent?
NOW already returns a TIMESTAMP, but the equivalent CURRENT_TIMESTAMP function can be used for improved clarity.
What is the MySQL date NOW equivalent?
NOW returns both the date and the time. If you want only the current date, you must call the MySQL CURRENT_DATE function.
