DATETIMEType is used when you need to include the date and time information at the same time. MySQL retrieves and displays DATETIME values in 'yyyy-MM-DD HH: MM: ss' format, the supported range is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '. ("Supported" means that although earlier values may work, they cannot be guaranteed .)
DATEType is used when you only need a date value and there is no time section. MySQL retrieves and displays the DATE value in 'yyyy-MM-DD 'format. The supported range is '2017-01-01' to '2017-12-31 '.
TIMESTAMPThe column type provides a type that you can use to automatically mark the INSERT or UPDATE operation with the current date and time.
TIMEData Type indicates the time in a day. MySQL retrieves and displays the TIME value in the format of "HH: MM: SS. The supported range is '00: 00: 00' to '23: 59: 59 '.
Create Table SQL
- CREATE TABLE'Tb _ test '(
- 'Id'Int(11)NOT NULLAUTO_INCREMENT,
- 'Date1'Timestamp NOT NULL DEFAULT '2017-00-00 00:00:00' ON UPDATECURRENT_TIM
- ESTAMP,
- 'Date2'Date DEFAULT NULL,
- 'Date3' datetimeDEFAULT NULL,
- 'Time'Time DEFAULT NULL,
- PRIMARY KEY('Id ')
- );
Running effect: