MySQL Date type datetime and timestamp

MySQL5 has three types of dates: datetime, date, and timestamp, except that date is used to indicate that a time is not taken, and the other two take seconds. The timestamp can also be accurate to milliseconds. Second, there is a common denominator, that is, their format is "not strict", very free, generally you think the right format can be inserted correctly into the database. Here are some of the most common problems with time-and-date dates. First, Imestamp1. The timestamp column must have a default value, which can be "0000-00-00 00:00:00", but cannot be null. 2, timestamp column can not set the value, can only be modified automatically by the database. 3. A table can have more than one timestamp column, but only one column will change to the current value of the database system based on data updates. Therefore, it makes no sense to have more than one timestamp column in a table, in fact a table only sets one timestamp column. 4. The default value of the timestamp column is the CURRENT_TIMESTAMP constant value. When the record data changes, the TIMESTAMP column automatically sets its value to Current_timestamp. 5, timestamp column after the creation of the format is: ' A 'timestamp notNULL DEFAULTCurrent_timestamp on UPDATE Current_timestamp, this statement means that the default value of the A field is Current_timestamp, and when the record is updated, the value of the A field is automatically set to Current_timestamp. 6, in addition, the following definition is syntactically correct, but meaningless, because the value of the field cannot be changed, and can always be the default value. ' B 'timestamp notNULL DEFAULT' 0000-00-00 00:00:00 ', Second, DATETIME1, the datetime column can be set to multiple, the default is NULL, you can set its value manually. 2, datetime column can not set default values, this is a lot of people painstakingly study the results, hehe! 3, the datetime column can be set in a disguised default value, such as through a trigger, or when inserting data, the DateTime field value is set to now (), this can be done, especially the latter, in the development of the program is often used. When creating a table, create time with datetime, update time with timestamp.CREATE TABLEUser(
Idbigint(20) not NULLAuto_increment,
name varchar(CHARACTER)SETGbk notNULL,
Sextinyint(1)DEFAULT ' 1 ',
Statesmallint(2)DEFAULT ' 1 ',
Createtimedatetime not NULL,
updatetime timestamp not NULL DEFAULTcurrent_timestampon UPDATE current_timestamp ,
PRIMARY KEY(ID)
) Engine=myisam Auto_increment=1DEFAULTCharset=latin1 three, the date of the maximum range of issuesThe issue of the maximum range of dates is not absolute, many people make a saying can not check the 20XX years, this is an egg, there is no such thing. The range of dates and other issues related to MySQL's operating mode. Of course, this range is very broad, enough bastard to use, do not worry about this problem. On the contrary, it is important to note that programming languages have different restrictions on date ranges, different languages, and no discussion here. Four, date format conversion1. Date of string transferSelectStr_to_date (' 2010-03-03 16:41:16 ','%y-%m-%d%h:%i:%s ') 2, date to stringSelectDate_format (' 2010-03-03 16:41:16 ','%y-%m-%d%h:%i:%s ') Five, the date of the day of the month and the week, and other methods of obtainingSelect TIMESTAMP(' 2010-03-03 16:41:16 ');

SelectDATE (' 2010-03-03 16:41:16 ');

Select Year(' 2010-03-03 16:41:16 ');

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

Select Day(' 2010-03-03 16:41:16 ');

SelectTime (' 2010-03-03 16:41:16 ');

SelectCurtime ();

SelectCurdate ();

Select current_date;

Select Current_time;

Select Current_timestamp; A lot of ways, here is a simple list of one or two. Vi. arithmetic operations of datesThe related functions are many and very simple to use, and it is recommended to check the MySQL reference manual for a look. Mysql>SELECTDate_add (' 1999-01-01 ', INTERVAL 1 Day);

-' 1999-01-02 '

Mysql>SELECTDate_add (' 1999-01-01 ', INTERVAL 1 HOUR);

-' 1999-01-01 01:00:00 '

Mysql>SELECTDate_add (' 1998-01-30 ', INTERVAL 1MONTH);

-' 1998-02-28 ' Seven, the date size comparisonTake the day when the number, holding the string when the date, oh, very simple. andUpdate_time >' 2010-03-02 16:48:41 '
andUpdate_time <=' 2010-03-03 16:51:58 '

MySQL Date type datetime and timestamp