Time type summary in MySql _ MySQL

Recently, the time data is used to create a table, but I have never understood the time data .. 1. DATE is a DATE. The supported range is 1000-01-01 to 9999-12-31. MySQL displays the date in a YYYY-MM-DD type, but allows time-type data to be used for the most recent table creation, but has never been familiar with time-type data ..

I,DateDATE

A date. Supported range:"1000-01-01"To"9999-12-31". MySQL displayDateUseYYYY-MM-DD"Format, but the value can be assigned using strings or numeric columnsDate.

1. in a stringYYYY-MM-DD"Or"Yy-mm-dd"Format. The "not strict" syntax allows any punctuation character to be used as a separator between dates. Is,2012-12-31",'2012/12/31',2012 ^ 12 ^ 31, And2012 @ 12 @ 31And2012-12-31"Is equivalent

2. as a string without any separator"Yyyymmdd"Format string, as long as it is a meaningful date. For example,20070523"And070523"Interpreted",071332"Is invalid (meaningless month/day)"0000-00-00".

II. date and timeDATETIME

· Combination of date and time. Supported range:"1000-01-0100:00:00"To9999-12-31 23:59:59". MySQL displayDate and timeUse'Yyyy-MM-DD HH: MM: SS'Format, but the value can be assigned using strings or numeric columnsDate and time.

· 1. allow "not strict" syntax: Any point operator can be used as a cut between the date part or the time part. For example, '98-12-31 11:30:45 'and '98. 12.3111 + 30 + 45', '2017/31 11*30 * 45', and '2017 11 ^ 30 ^ 45' are equivalent.

2. the numbers '123' and '123' are interpreted as '2017-05-19970523091528: 15: 28 ', but '123' is invalid (it has a meaningless minute part) and will change to '2017-00-00 00:00:00 '.

III. TIME

· Time range838: 59: 59"To838: 59: 59". Format: hh: MM: ss (or HHH: MM: ss ). The time value range can be "838: 59: 59" to "838: 59: 59 ". Time may be so large because the time type can be used not only to represent the time of a day (must be less than 24 hours ), but it also goes through the time or time interval between two events (which may be much larger than 24 hours, or even negative ).

Abbreviations1112"And1112Meaning11:12:00"(12 minutes after), but MySQL00:11:12"(11 minutes, 12 seconds ). Similarly,"12"And12Interpreted00:00:12 ".

L IV,TIMESTAMP

1. timestamp range"00:00:01"--"2038-01-19 03:14:07"UTC.

TIMESTAMPProvides automatic initialization and update to the current date and time.

Comparison between TIMESTAMP and DATETIME:

These two date and time types have their own advantages: datetime has a large date range; timestamp occupies a small storage space, only half of datetime. In addition, columns of the timestamp type have another feature: by default, the timestamp column is automatically filled/updated with the current time (CURRENT_TIMESTAMP) during insert and update data. "Automatic" means that MySQL will handle it for you without worrying about it.

Cx

5. YEAR type

YEAR is a single-byte type used to represent the YEAR.

MySQL retrieves and displays the YEAR value in YYYY format. The range is 1901 to 2155.

YEAR values in various formats can be specified:

· A four-character string in the range of '20180101' to '20180101 '.

· Four digits in the range of 1901 to 2155.

· Two strings in the range of '00' to '99 '. Values from '00' to '69' and from '70' to '99' are converted to YEAR values in the range of 2000 to 2069 and 1970 to 1999.

· Two integers in the range of 1 to 99. 1 to 69Converted2001To2069

The value between 70 and 99 is converted to the YEAR value in the range of 1970 to 1999.

Note that the range of two integers is slightly different from that of two strings, because you cannot directly set zero as a number and interpret it as 2000. You must specify it as a string '0' or '00' or it is interpreted as 0000.

· The result returned by the function. its value is applicable to the YEAR context, for example, NOW ().

The invalid YEAR value is converted to 0000.

Total format table:

DATATYPE VALUE

DATE	'2017-00-00'
TIME	'00: 00: 00'
DATETIME	'2017-00-00 00:00:00'
TIMESTAMP	'2017-00-00 00:00:00'
YEAR	0000