Date, DateTime, TimeStamp, and Time types in the MySQL database

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

1. CREATE TABLE'Tb _ test '(
2. 'Id'Int(11)NOT NULLAUTO_INCREMENT,
3. 'Date1'Timestamp NOT NULL DEFAULT '2017-00-00 00:00:00' ON UPDATECURRENT_TIM
4. ESTAMP,
5. 'Date2'Date DEFAULT NULL,
6. 'Date3' datetimeDEFAULT NULL,
7. 'Time'Time DEFAULT NULL,
8. PRIMARY KEY('Id ')
9. );

Running effect: