There are three types of time types: 1. TIME type, used for values that only require time information. It requires 3 bytes for storage, and the format is "HH:MM:SS"; 2. DATETIME type, For values that need to contain both date and time information, 8 bytes are required for storage, and the format is "YYYY-MM-DD HH:MM:SS"; 3. TIMESTAMP type, used for values that need to contain both date and time information. The value requires 4 bytes for storage, and the format is "YYYY-MM-DD HH:MM:SS".
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
There are many data types representing date and time in MySQL: YEAR, TIME, DATE, DTAETIME, TIMESTAMP. When only recording year information, you can only use the YEAR type.
Each type has a legal value range. When an illegal value is specified, the system will insert a "zero" value into the database.
There are three types that can represent time.
| Type name | Time format | Date range | Storage requirements |
|---|---|---|---|
| TIME | HH:MM:SS | -838:59:59 ~ 838:59:59 | 3 bytes |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8 bytes |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1980-01-01 00 :00:01 UTC ~ 2040-01-19 03:14:07 UTC | 4 bytes |
TIME type
TheTIME type is used for values that require only time information and requires 3 bytes for storage. The format is HH:MM:SS. HH represents hours, MM represents minutes, and SS represents seconds.
The value range of the TIME type is -838:59:59～838:59:59. The reason why the hour part is so large is that the TIME type can not only be used to represent the time of a day (must be less than 24 hours), It could also be the time since an event or the time between two events (which can be greater than 24 hours, or even negative).
TIME values can be specified using various formats, as shown below.
'D HH:MM:SS' format string. You can also use these "non-strict" syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH' or 'SS'. D here represents the day, which can take a value between 0 and 34. When inserting into the database, D is converted to hours and saved in the format "D*24 HH".
'HHMMSS' format, a string without separators, or a numeric value in HHMMSS format, assuming a meaningful time. For example, '101112' is understood as '10:11:12', but '106112' is not legal (it has a meaningless minutes part) and will become 00:00:00 when stored.
Tip: When assigning abbreviated values to the TIME column, please note: If there is no colon, MySQL assumes that the rightmost two digits represent seconds when interpreting the value. (MySQL interprets TIME values as past time rather than current time). For example, a reader might think '1112' and 1112 mean 11:12:00 (that is, 12 minutes after 11 o'clock), but MySQL interprets them as 00:11:12 (that is, 11 minutes and 12 seconds). Likewise '12' and 12 are interpreted as 00:00:12. On the contrary, if a colon is used in the TIME value, it is definitely regarded as the time of the day, that is, '11:12' means 11:12:00, not 00:11:12.
DATETIME type
The DATETIME type is used for values that need to contain both date and time information and requires 8 bytes for storage. The date format is 'YYYY-MM-DD HH:MM:SS', where YYYY represents the year, MM represents the month, DD represents the day, HH represents the hour, MM represents the minute, and SS represents the second.
When assigning values to fields of DATETIME type, you can use string type or numeric type data to insert, as long as it conforms to the date format of DATETIME, as shown below:
The date expressed in the string format of 'YYYY-MM-DD HH:MM:SS' or 'YYYYMMDDHHMMSS', the value range is '1000-01-01 00:00:00'～'9999-12-3 23 :59:59'. For example, if you enter '2014-12-31 05:05:05' or '20141231050505', the DATETIME value inserted into the database will be 2014-12-31 05:05:05.
A date represented in 'YY-MM-DD HH:MM:SS' or 'YYMMDDHHMMSS' string format, where YY represents a two-digit year value. Same as before, the year value in the range of '00~79' is converted to '2000~2079', and the year value in the range of '80~99' is converted to '1980~1999'. For example, if you enter '14-12-31 05:05:05', the DATETIME inserted into the database is 2014-12-31 05:05:05; if you enter 141231050505, the DATETIME inserted into the database is 2014-12-31 05:05:05 .
Date and time expressed in YYYYMMDDHHMMSS or YYMMDDHHMMSS numeric format. For example, if you enter 20141231050505, the DATETIME inserted into the database is 2014-12-31 05:05:05; if you enter 140505050505, the DATETIME inserted into the database is 2014-12-31 05:05:05.
Tip: MySQL allows "relaxed" syntax: any punctuation mark can be used as a separator between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11 30 35', '98/12/31 11*30*45' and '98@12@31 11^30^45' are Equivalently, these values can be correctly inserted into the database.
TIMESTAMP type
The display format of TIMESTAMP is the same as DATETIME, the display width is fixed at 19 characters, and the date format is YYYY-MM-DD HH:MM : SS, requires 4 bytes for storage. However, the value range of the TIMESTAMP column is smaller than the value range of DATETIME, which is '1970-01-01 00:00:01'UTC～'2038-01-19 03:14:07'UTC. When inserting data, make sure it is within the legal value range.
Tip: Coordinated Universal Time (English: Coordinated Universal Time, French: Temps Universel Coordonné) is also known as Universal Unified Time, World Standard Time, and International Coordinated Time. The English (CUT) and French (TUC) abbreviations are different, and as a compromise, the abbreviation is UTC.
TIMESTAMP and DATETIME, in addition to different storage bytes and supported ranges, the biggest difference is:
DATETIME When storing date data, Store according to the actual input format, that is, what is entered is stored, regardless of the time zone;
The TIMESTAMP value is stored in UTC (Coordinated Universal Time) format. The current time zone is converted when storing, and then converted back to the current time zone when retrieving. That is, when querying, the displayed time value is different depending on the current time zone.
Tip: If you assign a DATE value to a DATETIME or TIMESTAMP object, the time portion of the resulting value is set to '00:00:00', so the DATE value is not included time information. If a DATE object is assigned a DATETIME or TIMESTAMP value, the time portion of the resulting value is removed, so the DATE value contains no time information.
[Related recommendations:mysql video tutorial]
The above is the detailed content of What type of time is used in mysql?. For more information, please follow other related articles on the PHP Chinese website!