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

Main types: YEAR, TIME, DATE, DATETIME, TIMESTAMP

(date function: current_date (current date), current_time (current time),

Now () (current date and time, showing date or time based on field type))

Experimental timetest table structure

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. The DATETIME type, which contains the date and time sections. You can use either a quote string or a number, and the year can be 4 digits or 2 digits. If there is a need for such a requirement in the actual application, you can use the DATETIME type.

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.

The TIMESTAMP type and the DATETIME type are represented in the same format, storing 4 bytes (less than DATETIME), and the range of values is less than the DATETIME type.

The biggest difference between TIMESTAMP and DATETIME is that TIMESTAMP displays time based on time zone.

The timstamp field in the above figure changes with the time zone, but the datetime does not.

DATE

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

The DATE type can be represented in the following format:

'YYYY-MM-DD','YYYYMMDD',YYYYMMDD,'YY-MM-DD','YYMMDD',YYMMDD

When two digits are used to represent the year, it is divided into 000.69 and 700.99. Refer to the YEAR type.

TIME

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

TIME types can be represented in a variety of formats, such as:

'D HH:MM:SS':D represents the number of days, which is converted into hours when inserted into the table, that is, D*24+HH:MM:SS.

HHMMSS': omits the colon, but each time should be meaningful, for example, the '128012' minute part will be recognized as' 128012 'minute for 80 minutes is meaningless, the insertion will report an error.

'HH:MM':' means hours and minutes, for example,'12 HH:MM': 08 'means 12 hours and 8 minutes.

'SS': stands for seconds.

It should be noted that if you type '1208', although we want the MySQL to be recognized as 12 hours and 8 minutes, in this format, the MySQL is recognized as'00 12 08'.

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.

1. Expressed as a 4-digit number or a string, the two have the same effect, indicating a range of 1901-2155, and inserting data out of the range will report an error.

two。 Expressed in a 2-bit string format with a range of'00'. The word "69" means 2000 "2069" and "70"99" means "1970" 1999.' Both'0' and '00' will be identified as 2000, and out-of-range data will be identified as 2000.

3. Expressed in a 2-digit format with a range of 1x99. One hundred and sixty-nine means 2001, two hundred sixty nine, seven hundred ninety nine, one hundred ninety nine, one hundred ninety nine. However, the 0 value is recognized as 0000, which is different from the 2-bit string being recognized as 2000.

Insert into timetest (t_year) values (2018), ('2018'), ('00'), (00), (10), (' 10'), (77), ('77'), (' 0'), (0)

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.