MySQL date type DATETIME and TIMESTAMP

MySQL date type DATETIME and TIMESTAMP
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. 1. The IMESTAMP 1 and TIMESTAMP columns must have default values. The default values 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 format of the TIMESTAMP column is: 'A' timestamp not null default CURRENT_TIMESTAMP on 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 NOT NULL DEFAULT '2014-00-00 00:00:00 '. 2. You can set multiple DATETIME columns. The DEFAULT value can be 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 table user (
Id bigint (20) not null AUTO_INCREMENT,
Name varchar (20) character set gbk not null,
Sex tinyint (1) DEFAULT '1 ',
State smallint (2) DEFAULT '1 ',
Createtime datetime not null,
Updatetime timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
Primary key (id)
) ENGINE = MyISAM AUTO_INCREMENT = 1 default charset = latin1 3. the maximum range of dates is incorrect. The maximum range of dates is not absolute. Many people make a statement that they cannot check for 20XX years, this is not the case. 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 Conversion 1. string to date select STR_TO_DATE ('2017-03-03 16:41:16 ',' % Y-% m-% d % H: % I: % s') 2. Convert the date to the string select DATE_FORMAT ('2017-03-03 16:41:16 ',' % Y-% m-% d % H: % I: % s ') 5. select TIMESTAMP ('2017-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. There are many date arithmetic operations related functions and their usage is very simple. You can check the MySQL Reference Manual at a glance. Mysql> SELECT DATE_ADD ('2017-01-01 ', INTERVAL 1 DAY );

-> '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 );

-> '2014-02-28 '7. Compare the date size with a date number and a string as the date, which is very simple. And update_time> '2017-03-02 16:48:41'
And update_time <= '2017-03-03 16:51:58'

This article permanently updates the link address: