Operate Date and Time in MySQL

Abstract: Date and time functions are very useful for creating a site. The website owner is often interested in when data in a table is updated. Using Date and Time Functions, you can track changes to a table in seconds.

Date and time functions are very useful for creating a site. The website owner is often interested in when data in a table is updated. Using Date and Time Functions, you can track changes to a table in seconds.

The DATE and TIME types are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each of these values has a valid value range, and "zero" is used when you specify an invalid value. Note that MySQL allows you to store a valid "loose" date value, such as, because we think it is the responsibility of the application to handle date checks rather than SQL servers. To make the date check "faster", MySQL only checks the range of month in the range of 0-12 and day in the range of 0-31. The preceding range is defined as because MySQL allows you to store dates in a DATE or DATETIME column. The day or month here is zero. This is extremely useful for storing an application that you do not know the exact date of a birthday, in which case you simply store the date like or 1999-01-00. (Of course you cannot expect to get the correct value similar to those dates from functions such as DATE_SUB () or DATE_ADD ).

Returns the current date and time.

By using the GETDATE () function, you can obtain the current date and time. For example,

CURDATE () returns the current date

CURRENT_DATE

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

Mysql> select CURDATE ();

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

| CURDATE () |

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

| 2001-02-20 |

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

Mysql> select CURDATE () + 0;

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

| CURDATE () + 0 |

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

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

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

| 1, 104525 |

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

NOW () returns the current period and time

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

Mysql> select now ();

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

| Now () |

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

| 10:45:57 |

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

Mysql> select now () + 0;

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

| Now () + 0 |

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

| 1, 20010220105635 |

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

These functions obtain the current date and time, which is very convenient for date and time calculation, especially for calculating the time difference between a time and the present. For example, in the pet table, we calculate the age of pets in days:

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

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

| Name | CURDATE ()-birth |

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

| Fluffy | 1, 80016 |

| Clags | 69903 |

| Buffy | 119707 |

| Chirpy | 29309 |

| Fang | 1, 109393 |

| Bow.| 109389 |

| Whistler | 39011 |

| Slim | 49791 |

| Puffball | 19890 |

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

 

Automatically record the data change time

The TIMESTAMP column type is provided. The TIMESTAMP value can start from a certain time of 1970 until January 1, 2037. The precision is one second, and its value is displayed as a number. You can use it to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is automatically updated.

The first TIMESTAMP column is automatically updated under any of the following conditions:

The column is not explicitly specified in an INSERT or load data infile statement.

Columns are not explicitly specified in an UPDATE statement and some other columns change values. (Note that setting an UPDATE column as its existing value does not cause the TIMESTAMP column to be updated, because if you set a column as its current value, MySQL ignores the changes for efficiency .)

You explicitly set the TIMESTAMP column as NULL.

The TIMESTAMP column except the first one can also be set to the current date and time, as long as the column is set to NULL, or NOW ().

For example, create the following table:

Mysql> create table student
-> (
-> Id int,
-> Name char (16 ),
-> English tinyint,
-> Chinese tinyint,
-> History tinyint,
-> Time timestamp
-> );

Insert records into the table to view the results:

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

View the storage status of the record:

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 value of data input. If you update and modify the record, view the operation result:

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 |

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

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

Sometimes you want to modify the value of the TIMESTAMP column without changing any value. MySQL can automatically update the value of the TIMESTAMP column as long as the value of this column is set to NULL:

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 expected value, you can set any TIMESTAMP column as a value different from the current date and time, even for the first TIMESTAMP column. For example, if you want a TIMESTAMP to be set to the current date and time when you create a row, but it will not change at any time after the row is updated, you can use this method:

Let MySQL set the column when the row is created, which will initialize it as the current date and time.

When you perform subsequent changes to other columns in the row, specify the current value of the TIMESTAMP column.

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