What are the mysql date formats?

MySQL has 5 date formats, namely: 1. "YYYY-MM-DD HH:MM:SS" format; 2. "YYYYMMDDHHMMSS" format; 3. "YYYY-MM-DD" format; 4. “HH:MM:SS” format; 5. “YYYY” format.

The date and time types supported by mysql (5.5) are: DATETIME, TIMESTAMP, DATE, TIME, YEAR.

Comparison of several types is as follows:

## TIME 3 bytes HH:MM:SS -838:59:59838:59:59 00:00:00 YEAR 1 bytes YYYY1901 2155 0000

DATETIME

DATETIME is used to represent the year, month, day, hour, minute and second. It is a combination of DATE and TIME, and the recorded year (see the table above) is relatively long. If there is such a need in actual applications, you can use the DATETIME type.

TIMESTAMP

• TIMESTAMP is used to represent the year, month, day, hours, minutes and seconds, but the recorded year (see the table above) is relatively short.
• TIMESTAMP is related to the time zone and better reflects the current time. When inserting a date, it will be converted to the local time zone before being stored; when querying the date, the date will be converted to the local time zone before being displayed. So people in different time zones see the same time differently.
• The first TIMESTAMP column in the table is automatically set to the system time (CURRENT_TIMESTAMP). When a row is inserted or updated without explicitly assigning a value to the TIMESTAMP column, it will automatically be set to the current system time. If there is a second TIMESTAMP column in the table, the default value is set to 0000-00-00 00:00:00.
• TIMESTAMP properties are greatly affected by the Mysql version and server SQLMode.

If the recorded date needs to be used by people in different time zones, it is best to use TIMESTAMP.

DATE

DATE is used to represent the year, month and day. If the actual application value needs to save the year, month and day, you can use DATE.

TIME

TIME is used to represent hours, minutes and seconds. If the actual application value needs to save hours, minutes and seconds, you can use TIME.

YEAR

YEAR is used to represent the year. YEAR has 2-digit (preferably 4-digit) and 4-digit year formats. The default is 4 digits. If the actual application only saves the year, then it is perfectly fine to use 1 bytes to save the YEAR type. Not only can it save storage space, but it can also improve table operation efficiency.

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------

Each date and time type has a valid value range. If it exceeds this range, an error will be reported under the default SQLMode and the value will be zero. (See table above) Storage.

When inserting or updating, date and time types allow "relaxed" syntax, taking DATETIME as an example (other date and time types are similar):

• YYYY-MM-DD HH:MM :SS or YY-MM-DD HH:MM:SS format string. Any symbol can be used as a separator for the date part or time part. For example: "14-06-18 14:54:10", "14*06*18 14.54.10", "14 06 18 14=54=10" are equivalent. For string values ​​containing date and time, if the value of month, day, hour, minute, and second is less than 10, there is no need to specify two digits. For example: "2014-2-3 2:3:6" and "2014-02-03 02:03:06" are equivalent.

• A string in YYYYMMDDHHMMSS or YYMMDDHHMMSS format. A string can be interpreted as a datetime type if it is valid for a datetime type. For example: "20140618145410" and "140618145410" will be interpreted as "2014-06-18 14:54:10", but "20140618145480" is illegal (the number of seconds is illegal) and will be interpreted as "0000-00- 00 00:00:00".

• A number in YYYYMMDDHHMMSS or YYMMDDHHMMSS format. If the number is valid for a datetime type it can be interpreted as a datetime type. For example: "20140618145410" and "140618145410" will be interpreted as "2014-06-18 14:54:10". The length of the value should be 6, 8, 12, 14. If the value is 8 or 14 bits long, YYYYMMDD or YYYYMMDDHHMMSS format is assumed. If the value is 6 or 12 bits long, YYMMDD or YYMMDDHHMMSS format is assumed.

Recommended tutorial: mysql video tutorial

Date time type	Space occupied	Date format	Minimum value	Maximum value	A zero value means
DATETIME	8 bytes	YYYY- MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00- 00 00:00:00
TIMESTAMP	4 bytes	YYYYMMDDHHMMSS	19700101080001	2038 At some point	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000- 01-01	9999-12-31	0000-00-00

The above is the detailed content of What are the mysql date formats?. For more information, please follow other related articles on the PHP Chinese website!