mysql 出现多个now_MySQL NOW() 函数

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

Introduction to MySQL NOW() function

The MySQLNOW()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 theNOW()function depends on the context where it is used. For example, in the following statement, theNOW()function returns the current date and time as a string:SELECTNOW();

However, in the numeric context as the following example, theNOW()function returns the current date and time as a number:SELECTNOW()+0;

Notice that theNOW()function returns a constant date and time at which the statement started executing. See the following example:SELECTNOW(),SLEEP(5),NOW();

In the query, the firstNOW()function executed, and theSLEEP(5)function paused the execution of the query for 5 seconds, and the secondNOW()function executed. However, bothNOW()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 useSYSDATE()instead; see the following example:SELECTSYSDATE(),SLEEP(5),SYSDATE();

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

MySQL NOW() function calculations

Because theNOW()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:--mysqlnowminus1hourSELECT(NOW()-INTERVAL1HOUR)'NOW-1hour',NOW(),--mysqlnowplus1hourNOW()+INTERVAL1HOUR'NOW+1hour';

The following statement returns the current date and time, now minus 1 day and now plus 1 day:--mysqlnowminus1daySELECT(NOW()-INTERVAL1DAY)'NOW-1day',NOW(),--mysqlnowplus1day(NOW()+INTERVAL1DAY)'NOW+1day';

MySQL NOW() as a default value for a column

You can use theNOW()function as a default value for aDATETIMEorTIMESTAMPcolumn. When you omit the date or time value in theINSERT 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 namedtmpwith three columns:id,titleandcreated_on. Thecreated_oncolumn has default value specified by theNOW()function.CREATETABLEtmp(idINTPRIMARYKEYAUTO_INCREMENT,titleVARCHAR(255)NOTNULL,created_onDATETIMENOTNULLDEFAULTNOW()--orCURRENT_TIMESTAMP);

Notice thatCURRENT_TIMESTAMPandCURRENT_TIMESTAMP()are synonyms forNOW()so you can use them interchangeably.

Second, insert a new row into thetmptable without specifying the value for thecreated_oncolumn:INSERTINTOtmp(title)VALUES('TestNOW()function');

Third, query the data from thetmptable:SELECT*FROMtmp;

The value of thecreated_oncolumn has been updated to the current date and time at which theINSERTstatement executed.

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

原文链接：http://outofmemory.cn/mysql/function/mysql-now