Interpretation of mysql time functions sysdate () And now ()

Source: Internet
Author: User

Interpreting mysql time functions sysdate () And now () NOW () www.2cto.com return the current date and time value in the format of 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS, the specific format depends on whether the function is used in a string or a digital context. Mysql> select now ();-> '2017-12-15 23:50:26 'mysql> select now () + 0;-> 1997 in a bucket or trigger, NOW () returns a constant time, which indicates the time when the program or trigger statement starts to run. This is different from SYSDATE. SYSDATE () returns the current date and time value in the format of 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric context. In a storage program or trigger, SYSDATE () returns the execution time, rather than the time when the Chengdu or trigger statement starts to be executed. This NOW () operation is different. My understanding: now () is generated when the application initiates an execution statement to the mysql server (whether it is the SQL statement sent or the name of the stored procedure. Call now () in this stored procedure or in SQL to call this variable. The sysdate () function is retrieved in real time in our company's business. Because the use of stored procedures is prohibited, it is clear that both sysdate () And now () can meet business needs. Of course, considering that sysdate takes time in real time, I think now () is recommended. practice is the only criterion for testing truth: 1. stored Procedure: BEGIN select now (), sysdate (); select sleep (3); select now (), sysdate (); END execution result: mysql> call test; + rows + --------------------- + | now () | sysdate () | + period + hour + | 11:22:58 | 11:22:58 | + ----------------------- + ------------------------- + 1 row in set (0.00 sec) + ---------- + | Sleep (3) | + ---------- + | 0 | + ---------- + 1 row in set (3.00 sec) + --------------------- + ------------------- + | now () | sysdate () | + --------------------- + ------------------- + | 11:23:01 | 11:23:01 | + ------------------------- + rows + 1 row in set (3.01 sec) Query OK, 0 rows affected (3.01 sec) from the above results, the first now () and the second now () execution results are inconsistent. The now () function in the stored procedure is not taken from the execution start of the stored procedure.. 2. SQL statement test: mysql> select now (), sleep (3), now (); + --------------------- + ---------- + --------------------- + | now () | sleep (3) | now () | + response + ---------- + ------------------- + | 11:25:45 | 0 | 11:25:45 | + response + ---------- + ----------------------- + 1 row in set (3.00 sec) rating: now () function, the execution time starting from each query. mysql> select sysdate (), sleep (3), sysdate (); + ---------- ----------- + ---------- + ------------------- + | Sysdate () | sleep (3) | sysdate () | + --------------------- + ---------- + ------------------- + | 11:26:44 | 0 | 11:26:47 | + hour + ------------ + --------------------- + 1 row in set (3.02 sec) Comment: sysdate () the actual time is used each time. Based on the above results: 1. in most of the company's business, the now () function is enough, especially the time of the gmt_create and gmt_modified fields. now () 2 is definitely used. sysdate () is used to obtain the time in real time when each field needs to be executed. 3. mysql fields are executed from left to right.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.