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

DATETIME type is used when you need to contain both date and time information. MySQL retrieves and displays DATETIME in YYYY-MM-DDHH: MM: SS format. The supported range is 1000-01-0100:00:00 to 9999-12-3123: 59: 59. ("Support" means that, even though it is possible to work earlier, it cannot be guaranteed that they can .) You only need

DATETIME type is used when you need to contain both date and time information. MySQL retrieves and displays datetime in the 'yyyy-MM-DD HH: MM: ss' format. The supported range is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '. ("Support" means that, even though it is possible to work earlier, it cannot be guaranteed that they can .) You only need

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 NULL AUTO_INCREMENT,  `date1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,  `date2` date DEFAULT NULL,  `date3` datetime DEFAULT NULL,  `time` time DEFAULT NULL,  PRIMARY KEY (`id`));

Running effect:

References:
Date, DateTime, and TimeStamp types in the MySQL database


Mysql Tutorial: MySQL displays database data

Comparison between datetime and timestamp in MySQL