MySQL date types: DATETIME, TIMESTAMP, and datetimetimestamp

MySQL date types: DATETIME, TIMESTAMP, and datetimetimestamp
There are three types of dates in MySQL5: DATETIME, DATE, and TIMESTAMP, except that DATE is used to indicate a DATE without time and second, and the other two have time and second. TIMESTAMP can also be accurate to milliseconds. There is also a commonality, that is, their format is "not strict", very free, you generally think that the correct format can be correctly inserted into the database. Here we mainly solve some common problems with time, minute, and second dates.I. IMESTAMP1. The TIMESTAMP column must have a default value. The default value can be 0000-00-00 00:00:00, but cannot be null. 2. The value of the TIMESTAMP Column cannot be set. It can only be automatically modified by the database. 3. A table can have multiple TIMESTAMP columns, but only one column is changed to the current value of the database system based on data updates. Therefore, it is meaningless to have multiple TIMESTAMP columns in a table. In fact, only one TIMESTAMP column is set in a table. 4. The default value of the TIMESTAMP column is the constant value of CURRENT_TIMESTAMP. When the record data changes, the TIMESTAMP column automatically sets its value to CURRENT_TIMESTAMP. 5. The TIMESTAMP column is created in the format of 'A' timestamp NOTNULL DEFAULTCURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP. The default value of field a is CURRENT_TIMESTAMP. When the record is updated, the value of field a is automatically set to CURRENT_TIMESTAMP. 6. In addition, the following definition is correct from the syntax perspective, but it does not make sense, because the value of this field cannot be changed and can always be the default value. 'B' timestamp NOTNULL DEFAULT '2017-00-00 00:00:00 ',Ii. DATETIME1. You can set multiple DATETIME columns. The default value is null. You can set the value manually. 2. default values cannot be set for the DATETIME column. This is the result of painstaking research! 3. You can set the default value for the DATETIME column in disguise. For example, you can use a trigger or set the DATETIME field value to now () when inserting data. This is done, especially for the latter, it is often used in program development. Generally, the table creation time is datetime, and the update time is timestamp. CREATE TABLEuser (
Id bigint (20) not null AUTO_INCREMENT,
Name varchar (20) CHARACTERSET gbk NOTNULL,
Sex tinyint (1) DEFAULT '1 ',
State smallint (2) DEFAULT '1 ',
Createtime datetime not null,
Updatetime timestamp not null defacurrentcurrent_timestampon UPDATE CURRENT_TIMESTAMP,
Primary key (id)
) ENGINE = MyISAM AUTO_INCREMENT = 1 default charset = latin1Iii. Maximum date rangeThe problem with the maximum date range is not absolute. Many people say that they cannot check for 20XX years. The date range and other issues are related to the MySQL running mode. Of course, this range is very broad, and it is enough for the ancestor to take place. Don't worry about this problem. On the contrary, it should be noted that programming languages have different restrictions on the date range and different languages. We will not discuss them here.Iv. Date Format Conversion1. select STR_TO_DATE ('2017-03-03 16:41:16 ',' % Y-% m-% d % H: % I: % s ') 2. date to string select DATE_FORMAT ('2017-03-03 16:41:16 ',' % Y-% m-% d % H: % I: % s ')5. How to obtain the date, such as year, month, hour, minute, second, Week, and monthSelect TIMESTAMP ('2014-03-03 16:41:16 ');

Select DATE ('2014-03-03 16:41:16 ');

Select YEAR ('2014-03-03 16:41:16 ');

Select MONTH ('2014-03-03 16:41:16 ');

Select DAY ('2014-03-03 16:41:16 ');

Select TIME ('2014-03-03 16:41:16 ');

Select CURTIME ();

Select CURDATE ();

Select CURRENT_DATE;

Select CURRENT_TIME;

Select CURRENT_TIMESTAMP; there are many methods. Here we will briefly list one or two.6. arithmetic operations on datesThere are many related functions and the usage is very simple. You can check the MySQL Reference Manual at a glance. Mysql> SELECT DATE_ADD ('2017-01-01 ', INTERVAL 1DAY );

-> '2017-01-02'

Mysql> SELECT DATE_ADD ('2017-01-01 ', INTERVAL 1 HOUR );

-> '2017-01-01 01:00:00'

Mysql> SELECT DATE_ADD ('2017-01-30 ', INTERVAL 1 MONTH );

-> '2017-02-28'VII. Date size comparisonTake a day as a number and a string as a date. And update_time> '2017-03-02 16:48:41'
And update_time <= '2017-03-03 16:51:58'