Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to operate the date and time in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Date and time functions are very useful for building a site. Site owners are often interested in when the data in a table is updated. With the date and time functions, you can track changes in a watch in seconds.

The date and time types are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each of these has a range of legal values, and "zero" is used when you specify a really illegal value. Note that MySQL allows you to store a legal date value "loosely", such as 1999-11-31, because we think it is the responsibility of the application to handle date checking, not the SQL server. To make date checking "faster", MySQL only checks for months in the range of 0-12 and days in the range of 0-31. The above range is defined in this way because MySQL allows you to store dates in a DATE or DATETIME column, where the day or month is zero. This is extremely useful for applications that store a birthday where you don't know the exact date, in which case you simply store the date like 1999-00-00 or 1999-01-00. (of course you can't expect correct values similar to these dates from functions such as DATE_SUB () or DATE_ADD ()).

Returns the current date and time

Through the function GETDATE (), you can get the current date and time. For example,

CURDATE () returns the current date

CURRENT_DATE

Returns today's date value in YYYY-MM-DD or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

> select CURDATE ()

+-+

| | CURDATE () |

+-+

| | 2001-02-20 | |

+-+

Mysql > select CURDATE () + 0

+-+

| | CURDATE () + 0 |

+-+

| | 20010220 |

+-+

CURTIME () returns the current time

Returns the current time value in HH:MM:SS or HHMMSS format, depending on whether the function is used in a string or in the context of a number.

Mysql > select CURTIME ()

+-+

| | CURTIME () |

+-+

| | 10:42:38 |

+-+

Mysql > select CURTIME () + 0

+-+

| | CURTIME () + 0 |

+-+

| | 104525 |

+-+

NOW () returns the current period and time

NOW () returns date and time values in either YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format, depending on the context.

Mysql > select now ()

+-+

| | now () |

+-+

| | 2001-02-20 10:45:57 |

+-+

Mysql > select now () + 0

+-+

| | now () + 0 |

+-+

| | 20010220105635 | |

+-+

These functions that get the current date and time are convenient for the calculation of date and time, especially the time difference between a time and the present. For example, in the pet table, we calculate the age of the pet in days:

Mysql > SELECT name,CURDATE ()-birth FROM pet

+-+ +

| | name | CURDATE ()-birth |

+-+ +

| | Fluffy | 80016 | |

| | Claws | 69903 | |

| | Buffy | 119707 | |

| | Chirpy | 29309 | |

| | Fang | 109393 | |

| | Bowser | 109389 | |

| | Whistler | 39011 | |

| | Slim | 49791 | |

| | Puffball | 19890 | |

+-+ +

Automatically record the change time of the data

The TIMESTAMP column type provides a type where timestamp values can run from sometime in 1970 to 2037 with a precision of one second, and their values are displayed as numbers. You can use it to automatically mark INSERT or UPDATE operations with the current date and time. If you have more than one TIMESTAMP column, only the first one is automatically updated.

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

Column is not explicitly specified in an INSERT or LOAD DATA INFILE statement.

The column is not explicitly specified in one UPDATE statement and some other columns change the value. Note that a UPDATE sets a column to its existing value, which will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the change for the sake of efficiency. )

You explicitly set TIMESTAMP as NULL.

TIMESTAMP columns other than the first can also be set to the current date and time, as long as the column is set to NULL, or NOW ().

For example, create a table like this:

Mysql > CREATE TABLE student

-> (

-> id int

-> name char (16)

-> english tinyint

-> chinese tinyint

-> history tinyint

-> time timestamp

->)

Insert a record into the table to see the effect:

Mysql > INSERT student (id,name,englisht,Chinese,history) VALUES (11, "Tom", 66, 93 and 67)

View the storage of records:

Mysql > SELECT * FROM student

+-+ +

| | id | name | english | chinese | history | time | |

+-+ +

| | 11 | Tom | 66 | 93 | 67 | 20010220123335 | |

+-+ +

You can see that the time column records the time when the data was entered. If you update the change record, check the results of the operation:

Mysql > UPDATE student SET english=76 WHERE id=11

Mysql > SELECT * FROM student

+-+ +

| | id | name | english | chinese | history | time | |

+-+ +

| | 11 | Tom | 76 | 93 | 67 | 20010220125736 | |

+-+ +

You can clearly see that the time of the time column is automatically changed to the time when the record was modified.

Sometimes you want to modify the value of the TIMESTAMP column without changing any value, so you can automatically update the value of the TIMESTAMP column by setting the value of the column to NULL,MySQL:

Mysql > UPDATE student SET time=NULL WHERE id=11

Mysql > select * from student where id=11

+-+ +

| | id | name | english | chinese | history | time | |

+-+ +

| | 11 | Tom | 76 | 93 | 67 | 20010220130517 | |

+-+ +

By explicitly setting the desired value, you can set any TIMESTAMP column that is different from the current date and time, even for the first TIMESTAMP column. For example, if, when you create a row, you want a TIMESTAMP to be set to the current date and time, but does not change whenever the row is updated later, you can use this:

Have MySQL set the column when the row is created, which initializes it to the current date and time.

When you make subsequent changes to other columns in the row, explicitly set the TIMESTAMP column to its current value.

For example, when you modify a column, you can pay the original value to the TIMESTAMP column:

Mysql > UPDATE student SET english=66,time=time WHERE id=11

Mysql > select * from student where id=11

+-+ +

| | id | name | english | chinese | history | time | |

+-+ +

| | 11 | Tom | 66 | & nb |

After reading the above, do you have any further understanding of how to operate the date and time in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.