Interpretation of mysql time functions sysdate () and now () _ MySQL

Source: Internet
Author: User
Interpretation of mysql time functions sysdate () and now () bitsCN.com

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

NOW ()

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 a numeric context.

Mysql> select now ();

-> '2017-12-15 23:50:26'

Mysql> select now () + 0;

-> 19971215235026

In a stored program or trigger, NOW () returns a constant time, which indicates the start time of execution of the program or trigger statement. 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. the specific format depends 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 retrieves data in real time each 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;

+ --------------------- +

| Now () | sysdate () |

+ --------------------- +

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

+ --------------------- +

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 time of the stored procedure.

2. SQL statement test:

Mysql> select now (), sleep (3), now ();

+ --------------------- + ---------- + --------------------- +

| Now () | sleep (3) | now () |

+ --------------------- + ---------- + --------------------- +

| 11:25:45 | 0 | 11:25:45 |

+ --------------------- + ---------- + --------------------- +

1 row in set (3.00 sec)

Comment: now () function, the execution time of each query.

Mysql> select sysdate (), sleep (3), sysdate ();

+ --------------------- + ---------- + --------------------- +

| Sysdate () | sleep (3) | sysdate () |

+ --------------------- + ---------- + --------------------- +

| 11:26:44 | 0 | 11:26:47 |

+ --------------------- + ---------- + --------------------- +

1 row in set (3.02 sec)

Rating: sysdate () takes the actual time 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 ()

2. 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.

BitsCN.com

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.