The difference between Mysql Date,datetime and the conversion

Ref: 62885104/

These three time types have been somewhat unclear in the database.

Today, it is a very good understanding of the difference.

Date: You can directly understand the 2017-3-21 without time and seconds.

DateTime: On the contrary, it is a time and seconds

Timestamp: The timestamp is well understood (January 01, 1970 00:00 00 seconds (Beijing time January 01, 1970 08:00 00 seconds) up to now the total number of seconds. ）

There are issues that refer to the time range:

Time range

Date-> ' 1000-01-01 ' to ' 9999-12-31 '.

DateTime--' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '.

Timestamp--> ' 1970-01-01 00:00:01 ' UTC to ' 2038-01-19 03:14:07 ' UTC

Common date functions and conversions

1. Get the current date:
Current_date () or curdate ()

2. Get the current time:
Current_time () or Curtime ()

3. Get the current date and time
Now () or Current_timestamp ()

4. Conversion between Linux/unix timestamp and MySQL time date type:
Unix_timestamp (now ())//Convert the datetime of MySQL to Linux/unix timestamp; Date Time
Unix_timestamp (date ())//date of MySQL is converted to Linux/unix.
Unix_timestamp (now ())//time to convert MySQL to Linux/unix. (with question)
From_unixtime (time_t)//Converts a UNIX timestamp to a datetime of MySQL; date time
Date (From_unixtime (time_t))//Date
Time (From_unixtime (time_t))//Times

The difference between Mysql Date,datetime and the conversion