MySQL NOW() Function

Summary: in this tutorial, you will learn how to use the MySQL NOW() function to get the current date and time of the server.

Introduction to MySQL NOW() function

The MySQL NOW() function returns the current date and time in the configured time zone as a string or a number in the 'YYYY-MM-DD HH:MM:DD' or 'YYYYMMDDHHMMSS.uuuuuu' format.

Here’s the syntax of the NOW() function:

NOW()Code language: SQL (Structured Query Language) (sql)

The returned type of the NOW() function depends on the context where you use it.

For example, in the following statement, the NOW() function returns the current date and time as a string:

SELECT NOW();Code language: SQL (Structured Query Language) (sql)

Try It Out

+---------------------+
| NOW()               |
+---------------------+
| 2023-12-30 21:10:54 |
+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

However, in the numeric context, the NOW() function returns the current date and time as a number as shown in the following example:

SELECT NOW() + 0;Code language: SQL (Structured Query Language) (sql)

Try It Out

+----------------+
| NOW() + 0      |
+----------------+
| 20231230211120 |
+----------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Notice that the NOW() function returns the date and time at which the statement started executing. For example:

SELECT NOW(), SLEEP(5), NOW();Code language: SQL (Structured Query Language) (sql)

Try It Out

+---------------------+----------+---------------------+
| NOW()               | SLEEP(5) | NOW()               |
+---------------------+----------+---------------------+
| 2023-12-30 21:11:36 |        0 | 2023-12-30 21:11:36 |
+---------------------+----------+---------------------+
1 row in set (5.02 sec)Code language: JavaScript (javascript)

In the query, the first NOW() function executed, and the SLEEP(5) function paused the execution of the query for 5 seconds, and the second NOW() function executed.

However, both NOW() functions return the same value, even when they are executed at different times.

If you want to get the exact time at which the statement executes, you use the SYSDATE() function instead. For example:

SELECT SYSDATE(), SLEEP(5), SYSDATE();Code language: SQL (Structured Query Language) (sql)

Try It Out

+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(5) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2023-12-30 21:11:59 |        0 | 2023-12-30 21:12:04 |
+---------------------+----------+---------------------+
1 row in set (5.01 sec)Code language: JavaScript (javascript)

If you want to change the MySQL server’s time zone to adjust the current date and time returned by the NOW() function, you use the following statement:

SET time_zone = your_time_zone;Code language: SQL (Structured Query Language) (sql)

MySQL NOW() function calculations

Because the NOW() function returns a number when it is used in a numeric context, you can use it in calculations e.g., now plus 1 hour, now minus 1 hour, and now plus 1 day.

The following statement returns the current date and time, now minus 1 hour and now plus 1 hour:

-- mysql now minus 1 hour
SELECT 
  (NOW() - INTERVAL 1 HOUR) 'NOW - 1 hour', 
  NOW(), 
-- mysql now plus 1 hour
  NOW() + INTERVAL 1 HOUR 'NOW + 1 hour';Code language: SQL (Structured Query Language) (sql)

Try It Out

+---------------------+---------------------+---------------------+
| NOW - 1 hour        | NOW()               | NOW + 1 hour        |
+---------------------+---------------------+---------------------+
| 2023-12-30 20:12:23 | 2023-12-30 21:12:23 | 2023-12-30 22:12:23 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following statement returns the current date and time, now minus 1 day and now plus 1 day:

SELECT (NOW() - INTERVAL 1 DAY) 'NOW - 1 day',
        NOW(),
        (NOW() + INTERVAL 1 DAY) 'NOW + 1 day';Code language: SQL (Structured Query Language) (sql)

Try It Out

+---------------------+---------------------+---------------------+
| NOW - 1 day         | NOW()               | NOW + 1 day         |
+---------------------+---------------------+---------------------+
| 2023-12-29 21:12:37 | 2023-12-30 21:12:37 | 2023-12-31 21:12:37 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Using the NOW() function as the default value for a column

You can use the NOW() function to provide a default value for a DATETIME or TIMESTAMP column.

When you omit the date or time value in the INSERT statement, MySQL automatically inserts the current date and time into the column whose default value is NOW(). For example:

First, create a new table called contacts:

CREATE TABLE contacts(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL DEFAULT NOW(),
    updated_at DATETIME DEFAULT NOW() ON UPDATE NOW()
);Code language: SQL (Structured Query Language) (sql)

The contacts table includes the following columns:

id : This is the primary key column of the contacts table.
name: This represents the name of the contact.
email: This column stores the contact’s email.
created_on : This column has the default value specified by the NOW() function.
updated_at: This column sets updated_at with a default value of the current date and time when a new row is inserted. Additionally, the ON UPDATE NOW() clause ensures that the column is automatically updated to the current date and tie whenever the row is updated.

Notice that CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW() so you can use them interchangeably.

Second, insert a new row into the contacts table:

INSERT INTO contacts(name, email)
VALUES('Jane Doe', '[email protected]');Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the contacts table:

SELECT * FROM contacts;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------+----------------------+---------------------+---------------------+
| id | name     | email                | created_at          | updated_at          |
+----+----------+----------------------+---------------------+---------------------+
|  1 | Jane Doe | [email protected] | 2023-12-30 21:36:09 | 2023-12-30 21:36:09 |
+----+----------+----------------------+---------------------+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

In the INSERT statement, we don’t provide the values for the created_at and updated_at columns. Therefore, they use the default value which is the current date and time returned by the NOW() function.

Fourth, update the row with id 1:

UPDATE contacts
SET name = 'Jane Smith'
WHERE id = 1;Code language: JavaScript (javascript)

In the UPDATE statement, we don’t specify the value for the updated_at column so it is updated to the time the row is updated.

Finally, retrieve the data from the contacts table:

SELECT * FROM contacts
WHERE id = 1;

Output:

+----+------------+----------------------+---------------------+---------------------+
| id | name       | email                | created_at          | updated_at          |
+----+------------+----------------------+---------------------+---------------------+
|  1 | Jane Smith | [email protected] | 2023-12-30 21:36:09 | 2023-12-30 21:37:21 |
+----+------------+----------------------+---------------------+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

Use the NOW() function to get the current date and time of the server.
Use the DEFAULT NOW() for a column to set the default value for the column to the current date and time when a row is inserted.
Use the ON UPDATE NOW() for a column to set a default for a column to the current date and time when a row is updated.

Was this tutorial helpful?