MySQL Time type

There are several types of data that represent dates and times in MySQL. Where year represents the years, date represents the dates, time represents the times, and datetime and timestamp represent dates and practices. Their comparison is as follows

Year, byte number 1, Value range "1901--2155"
DATE, byte number 4, Value range "1000-01-01--9999-12-31"
Time, byte number 3, value range " -838:59:59--838:59:59"
DATETIME, byte number 8, value range "1000-01-01 00:00:00--9999-12-31 23:59:59"
TIMESTAMP, byte number 4, Value range "19700101080001--20380119111407"
When the insertion value exceeds a valid range of values, the system will error and insert a value of 0 into the database.

2. Year Type
   There are three ways to copy the year type.
   The first is to insert a 4-bit string or a 4-bit number directly.
   The second is to insert a 2-bit string, in which case inserting ' 00 ' ~ ' 69 ' would be equivalent to inserting 2000~2069, or inserting ' 70 ' ~ ' 99 ', which would be equivalent to insert 1970~1999. In the second case, if it is ' 0 ', it is the same as inserting ' 00 ', which represents 2000.
   The third is to insert a 2-digit number, which differs from the second (inserting a two-bit string) except that if you insert a number 0, it is 0000, not 2000. So when assigning a value to year type, be sure to distinguish between 0 and ' 0 ', although the difference between the two quotes, but the actual effect is indeed a difference of 2000 years.

4. Time Type
   The time type is expressed as "when: minutes: Seconds", although the hour range is generally 0~23, but in order to represent some special time intervals, MySQL expands the hour range of the hours and supports negative values.
   Assigning a value to the time type, the standard format is ' HH:MM:SS ', but not necessarily in this format.
   If the ' D HH:MM:SS ' format is inserted, it is similar to ' (D*24+HH): Mm:ss '. Inserting ' 2 23:50:50 ', for example, is equivalent to inserting ' 71:50:50 '.
   If you insert a ' hh:mm ' or ' SS ' format, the effect is to assign a value of zero to other values that are not represented by a bit. For example, insert ' 30 ', which is equivalent to inserting ' 00:00:30 ', if inserting ' 11:25 ', it is equivalent to inserting ' 11:25:00 '.
   You can also insert ' d HH ' and ' d hh:mm ', which can be inferred from the above example.
   In MySQL, for the ' HHMMSS ' format, the system can be automatically converted to a standard format.
   If we want to insert the time of the current system, we can insert current_time or now (). The time type only accounts for 3 bytes, which is most appropriate if you are only storing the temporal data.

6. Date type
   MySQL is a value that displays the date type in the YYYY-MM-DD format, which can be persisted when data is inserted. In addition, MySQL also supports some of the non-strict syntax format, the delimiter "-" can be used "@", "." And so many rich to replace.
   You can also use the "YY-MM-DD" format when inserting data, and the rules for converting YY to the corresponding year are similar to the years type.
   If we want to insert the time of the current system, we can insert current_date or now ().

8. DateTime type
   The standard format is "Yyyy-mm-dd HH:MM:SS", and the specific assignment method is similar to the methods of the above types.

10. Timestamp type
    Timestamp the range of values is small, no datetime value range is large, so enter the value must be guaranteed within the range of timestamp. Its insertion is also similar to inserting other date and time data types.
    So how does the timestamp type insert the current time? First, you can use Current_timestamp; second, enter NULL, the system automatically enters the current TIMESTAMP; third, without any input, the system automatically enters the current TIMESTAMP.
    There is a special point: the value of timestamp is associated with the time zone.

MySQL Time type