Three major differences between datetime and timestamp in MySQL date functions

In MySQL, the difference between the date function datetime and timestamp will be very detailed. The following section will summarize the comparison between datetime and timestamp in mysql.

Same: Display format consistent: YYYY-MM-DD HH: MM: SS;

Different:

Range: The range supported by datetime is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 ′

The TIMESTAMP value cannot be earlier than 1970 or later than 2037

Storage:

TIMESTAMP

Storage of 1.4 bytes (Time stamp value is stored in 4 bytes)
2. Save the value in UTC format (it stores the number of milliseconds)
3. Time Zone conversion. during storage, the current time zone is converted, and then retrieved to the current time zone.

The timestamp value of the timestamp column type can start from a certain time of 1970 until January 1, 2037. The precision is one second, and its value is displayed as a number.

The format of the timestamp value display size is as follows:
+ ------------------- + ------------------------ +
| Column type | display format |
| Timestamp (14) | yyyymmddhhmmss |
| Timestamp (12) | yymmddhhmmss |
| Timestamp (10) | yymmddhhmm |
| Timestamp (8) | yyyymmdd |
| Timestamp (6) | yymmdd |
| Timestamp (4) | yymm |
| Timestamp (2) | yy |
+ ------------------- + ------------------------- +

The "complete" timestamp format is 14 bits, but the timestamp column can also be created with a shorter display size.

The most common Display sizes are 6, 8, 12, and 14.

You can specify an arbitrary display size when creating a table, but the defined column length is 0 or greater than 14, and the column length is forcibly defined as 14.

The column length ranges from 1 ~ The dimensions of odd values in the range of 13 are forced to be the next larger even number.

Example:

Define Field Length Force Field Length

Timestamp (0)-> timestamp (14)
Timestamp (15)-> timestamp (14)
Timestamp (1)-> timestamp (2)
Timestamp (5)-> timestamp (6)

All timestamp columns have the same storage size. The full precision (14 bits) of the specified period time value is used to store valid values regardless of the display size.

An invalid date will be forcibly stored as 0

Datetime

1.8 bytes (8 bytes storage)
2. Storage in actual format (Just stores what you have stored and retrieves the same thing which you have stored .)
3. It has nothing to deal with the TIMEZONE and Conversion .)

Method 1:

 

You can also:
Select * from t1 where unix_timestamp (time1)> unix_timestamp ('2017-03-03 17:39:05 ') and unix_timestamp (time1) <unix _
Timestamp ('2017-03-03 17:39:52 ');
The unix_timestamp function is used to convert the Time of the Period type into a unix timestamp. I personally think this is more practical.

 

Method 2:

 

Time1 between '2017-03-03 17:39:05 'and '2017-03-03 17:39:52 ';

 

Method 3:

The datetime type can be converted to the date type before comparison.
Example: convert (date, table name. datetime column name)> = convert (date, table name. datetime column name)

 

Three methods to be verified, in short, do not use strings so direct comparison

DEFAULT Value: the DEFAULT value of TIMESTAMP is not NULL. It can be set to the DEFAULT current time. null default null on update CURRENT_TIMESTAMP
The default value of datetime can be set to null.