Differences between datetime, date, and timestamp types in MySQL

First, DATETIME

Display format: Yyyy-mm-dd HH:MM:SS
Time range: [' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 ']

Second, DATE

Display format: YYYY-MM-DD
Time range: [' 1000-01-01 ' to ' 9999-12-31 ']

Third, TIMESTAMP

Display format: Yyyy-mm-dd HH:MM:SS
Time range: [' 1970-01-01 00:00:00 ' to ' 2037-12-31 23:59:59 ']

Timestamp Note the point:

TIMESTAMP DEFAULT current_timestamp on UPDATE current_timestamp refreshes this data column when creating new records and modifying existing records.
TIMESTAMP Default Current_timestamp sets This field to the current time when creating a new record, but does not refresh it later when it is modified.
TIMESTAMP on UPDATE current_timestamp sets this field to 0 when creating a new record, and refreshes it later when it is modified.
TIMESTAMP default ' Yyyy-mm-dd hh:mm:ss ' on UPDATE current_timestamp set this field to the given value when creating a new record, and refresh it later when it is modified

1. When the timestamp column is not empty, the default value can be "0000-00-00 00:00:00", but cannot be null.
2. A table can have more than one TIMESTAMP column, but a table with only one TIMESTAMP type can be changed to the current value of the database system in the default or update section with Current_timestamp, which is set to data update.
3. 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.
4. The format after the timestamp column is created is:
ALTER TABLE ' Course '
ADD COLUMN ' birthday ' timestamp not NULL the DEFAULT current_timestamp on UPDATE current_timestamp;

ALTER TABLE ' Course '
ADD COLUMN ' birthday ' timestamp not NULL the DEFAULT ' 0000-00-00 00:00:00 ' on UPDATE current_timestamp;

ALTER TABLE ' Course '
ADD COLUMN ' birthday ' timestamp NULL after ' cname ';

Four, date format conversion
1. Date of string transfer
Select Str_to_date (' 2013-01-29 13:49:18 ', '%y-%m-%d%h:%i:%s ')
2. Date to String
Select Date_format (' 2013-01-29 13:49:18 ', '%y-%m-%d%h:%i:%s ')

Five, the date of the usual month and day of the month and the week, and other methods of obtaining
Select TIMESTAMP (' 2013-01-29 13:50:27 ');
Select DATE (' 2013-01-29 13:50:27 ');
Select year (' 2013-01-29 13:50:27 ');
Select MONTH (' 2013-01-29 13:50:27 ');
Select WEEK (' 2013-01-29 13:50:27 ');
Select Day (' 2013-01-29 13:50:27 ');
Select time (' 2013-01-29 13:50:27 ');
Select Curtime ();
Select Curdate ();
Select Current_date;
Select Current_time;
Select Current_timestamp;
Select Now ()

Vi. operation of the date:
SELECT date_add (' 2013-01-29 13:50:27 ', INTERVAL 1 day);
' 2013-01-30 13:50:27 '
SELECT date_add (' 2013-01-29 13:50:27 ', INTERVAL 1 HOUR);
' 2013-01-29 14:50:27 '
SELECT date_add (' 2013-01-29 13:50:27 ', INTERVAL 1 MONTH);
' 2013-02-28 13:50:27 '

Differences between datetime, date, and timestamp types in MySQL