MySQL Date function datetime and timestamp 3 big difference

Same: Consistent display format: YYYY-MM-DD HH:MM:SS;

Different:

Scope: DateTime support range for ' 1000-01-01 00:00:00′ to ' 9999-12-31 23:59:59′

Timestamp value cannot be earlier than 1970 or later than 2037

Stores:

TIMESTAMP

1.4-byte storage (time stamp value was stored in 4 bytes)
2. Value is saved in UTC (It stores the number of milliseconds)
3. Time zone conversion, storage time for the current time zone conversion, retrieval and then converted back to the current time zone.

The timestamp column type timestamp value can be from the beginning of 1970 to 2037, with a precision of one second, with a value displayed as a number.

The timestamp value displays the format of the dimension as shown in the following table:
+-------------------+------------------------+
| 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 "full" timestamp format is 14-bit, 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 defining a column length of 0 or greater than 14 is enforced as column length 14.

The column length is coerced to the next larger even number in the 1~13 range from the odd numeric dimension.

Columns such as:

Define field length force field length

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

All timestamp columns have the same storage size, using the full precision (14-bit) of the specified period time value to store the valid values regardless of the display size.

Illegal date, will be forced to 0 storage

Datetime

1.8 bytes Storage (8 bytes storage)
2. Actual format storage (Just stores what you have stored and retrieves the same thing for you which have.)
3. Time zone Independent (It has nothing to deal with the TIMEZONE and conversion.)

Method One:

You can also:
SELECT * from t1 where Unix_timestamp (time1) > Unix_timestamp (' 2011-03-03 17:39:05 ') and Unix_timestamp (TIME1) < Uni X_
Timestamp (' 2011-03-03 17:39:52 ');
is to use the Unix_timestamp function to turn the character time into a Unix timestamp. Personally feel that this more practical.

Method Two:

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

Method Three:

A datetime type can be converted to a date type and then compared
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 directly than

Default value: TIMESTAMP default value is not NULL, can be set to the default current time, null default NULL on UPDATE current_timestamp
The datetime default value can be set to NULL