Shulou(Shulou.com)06/01 Report--

This article mainly introduces what are the five date formats in mysql, which can be used for reference by friends who need them. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

There are five mysql date formats: 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-time types supported by mysql are: DATETIME, TIMESTAMP, DATE, TIME, YEAR.

Several types are compared as follows:

Date time type occupies space date format minimum value maximum value zero value represents DATETIME8 bytesYYYY-MM-DD HH:MM:SS1000-01-01 0000 00000000000000DATE4 bytesYYYY-MM-DD1000 009999-12-31 23 00000000000000DATE4 bytesYYYY-MM-DD1000 59000-00-0000: sometime in the 00:00TIMESTAMP4 bytesYYYYMMDDHHMMSS197001010800012038 year 00000000000000DATE4 bytesYYYY-MM-DD1000-01-019999-12-310000-00-00TIME3 bytesHH:MM:SS-838:59:59838:59:5900:00:00YEAR1 bytesYYYY190121550000

DATETIME

DATETIME is used to represent the year, month, day, hour and second, which is a combination of DATE and TIME, and the recorded year (see table above) is relatively long. If there is such a requirement in the actual application, the DATETIME type can be used.

TIMESTAMP

TIMESTAMP is used to represent the year, month, day, hour and second, but the recorded year (see table above) is relatively short. TIMESTAMP is related to the time zone and better reflects the current time. When inserting a date, it is converted to the local time zone before it is stored; when the date is queried, the date is converted to the local time zone before it is displayed. So people in different time zones see different things at the same time. The first TIMESTAMP column in the table is automatically set to system time (CURRENT_TIMESTAMP). When a row is inserted or updated, but the TIMESTAMP column is not explicitly assigned a value, it is also automatically set to the current system time. If there is a second TIMESTAMP column in the table, the default is set to 0000-00-0000: 00:00. The properties of TIMESTAMP 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 be saved, you can use DATE.

TIME

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

YEAR

YEAR is used to represent years, and YEAR has 2-bit (preferably 4-bit) and 4-bit format years. The default is 4 digits. If the actual application only saves the year, then saving the YEAR type with 1 bytes is fine. It can not only save storage space, but also improve the operation efficiency of the table.

- -

Each date-time type has a valid range of values beyond which an error is reported under the default SQLMode and stored as a zero value (see table above).

When inserting or updating, the date-time type allows "lax" syntax, such as DATETIME (other date-time types are the same):

A string in YYYY-MM-DD HH:MM:SS or YY-MM-DD HH:MM:SS format. Any symbol can be used as a spacer for the date part or the time part. For example, "14-06-18 14:54:10", "14-06-18 14-54-10" and "14-06-18 14-54-10" are equivalent. For string values that contain date and time, if the value of month, day, hour, minute, and second is less than 10, you do not 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. If the string is legal for the date-time type, it can be interpreted as the date-time type. For example, "20140618145410" and "140618145410" will be interpreted as "2014-06-1814: 54:10", but "20140618145480" is illegal (illegal number of seconds) and will be interpreted as "00000000000: 00:00". A number in YYYYMMDDHHMMSS or YYMMDDHHMMSS format. If the number is legal for the datetime type, it can be interpreted as the datetime type. For example, "20140618145410" and "140618145410" will be interpreted as "2014-06-1814: 54:10". The length of the value should be 6, 8, 12, 14. If the numeric length is 8 or 14 bits long, the YYYYMMDD or YYYYMMDDHHMMSS format is assumed. If the value is 6 or 12 bits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format. Thank you for reading this article carefully. I hope it is helpful for everyone to share the contents of the five date formats in mysql. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.