返回顶部

OutOfMemory.CN技术专栏-> MySQL-> mysql函数-> MySQL NOW() 函数

MySQL NOW() 函数

更多

Summary: in this tutorial, you will learn about the MySQL NOW() function and apply the NOW function in various contexts.

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.

The returned type of the NOW() function depends on the context where it is used. For example, in the following statement, the NOW() function returns the current date and time as a string:

SELECT NOW();

MySQL NOW function

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

SELECT NOW() + 0;

MySQL NOW function returns a number

Notice that the NOW() function returns a constant date and time at which the statement started executing. See the following example:

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

MySQL NOW execute multiple times

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 though they executed at the different times.

If you want to get exact time at which the statement executes, you need to use SYSDATE() instead; see the following example:

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

MySQL SYSDATE function

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;

 

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, now plus 1 day and now minus in day, etc.

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';

MySQL NOW minus 1 hour, now plus 1 hour

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

-- mysql now minus 1 day
SELECT (NOW() - INTERVAL 1 DAY) 'NOW - 1 day',
        NOW(),
        -- mysql now plus 1 day
        (NOW() + INTERVAL 1 DAY) 'NOW + 1 day';

MySQL NOW minus 1 day, now  plus 1 day

MySQL NOW() as a default value for a column

You can use the NOW() function as a default value for a DATETIME or TIMESTAMP column. When you omit the date or time value  in the INSERT statement, MySQL inserts the current date and time into the column whose default value is   NOW() .

Let’s take a look at the following example.

First, create a new table named tmp with three columns: id, title and  created_on. The created_on column has default value specified by the NOW() function.

CREATE TABLE tmp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    created_on DATETIME NOT NULL DEFAULT NOW() -- or CURRENT_TIMESTAMP
);

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

Second, insert a new row into the tmp table without specifying the value for the created_on column:

INSERT INTO tmp(title)
VALUES('Test NOW() function');

Third, query the data from the tmp table:

SELECT * FROM tmp;

MySQL NOW as default value

The value of the created_on column has been updated to the current date and time at which the INSERT statement executed.

In this tutorial, we have introduced you to MySQL NOW() function that returns the current date and time at which the statement executed.

推荐阅读:
支持

0

反对

0