MySQL in date and time type

1st period Type

There are date/datetime/timestamp three types of date types in MySQL.

When a date is assigned, the "not strict" syntax is allowed: Any punctuation character can be used as a break between a date part or a time part. For example, ' 98-12-31 11:30:45 ', ' 98.12.31 11+30+45 ', ' 98/12/31 11*30*45 ' and ' [email protected]@31 11^30^45 ' are equivalent, For illegal will be converted to: 0000-00-00 00:00:00

1.1 Date format

This type of field, stored in data format: YYYY-MM-DD, which supports a range of ' 1000-01-01 ' to ' 9999-12-31 ', and allows the use of strings or numbers to replicate for this column.

For example, the date format field is Crt_date, and the result is copied by string and number as follows:

Update Set = 12331212 where = ' sys_dict ' ; Update Set = ' 1233-12-12 ' where = ' Sys_dict_item ';

1.2 DateTime format

A combination of date and time, stored in the format: Yyyy-mm-dd HH:MM:SS, which supports a range of ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 ', and allows assigning values using strings or numbers.

For example:

Update Set = 12331212121212 where = ' sys_dict ' ; Update Set = ' 1233-12-12 12:12:12 ' where = ' Sys_dict_item ';

1.3 Timestamp format

This is the timestamp, from 1970-01-01 00:00:00 to the current time difference value (Note: When you output [new Date (0)] in Java, the output is: Thu Jan 08:00:00 CST 1970, this is because the concept of time zone, China is the East eight district, So the corresponding is eight in the morning), it is accurate to the millisecond level, the range is: 1970-01-01 00:00:00 to 2037 (refer to the 2038 issue), the value of more than 2037 will be thrown: [Error code:1292, SQL state:22001] Data Truncation:incorrect datetime value: ' 20381212121212 ' for column ' Crt_date ' at row 1. Setting a value allows you to set only the value of a numeric type.

When assigning a value to the timestamp Type field, the value must be greater than 19700101000000, or it will be thrown: [Error code:1292, SQL state:22001] Data truncation: Incorrect datetime value: ' 10831212121212 ' for column ' Crt_date ' at row 1.

For example:

Update Set = 20081212121212 where = ' sys_dict ';

2 Time Type

The time type in MySQL is expressed in times.

2.1 Time Format

MySQL retrieves and displays the time value in the ' HH:MM:SS ' format (or the ' HHH:MM:SS ' format for large values). The time value can range from ' -838:59:59 ' to ' 838:59:59 '. The number of hours is therefore large because the time type can be used not only to represent the day (must be less than 24 hours), but also to the time interval between an event's past or two events (which can be greater than 24 hours, or even negative).

Format Description:

• A string in the ' D HH:MM:SS.fraction ' format. You can also use any of the following "non-strict" syntax: ' HH:MM:SS.fraction ', ' HH:MM:SS ', ' hh:mm ', ' d HH:MM:SS ', ' d ' hh:mm ', ' d HH ' or ' SS '. Here d is the day and can take a value between 0 and 34. Please note that MySQL does not save fractions yet.
• The ' HHMMSS ' format has no inter-cut string, which is assumed to have a meaningful time. For example, ' 101112 ' is understood as ' 10:11:12 ', but ' 109712 ' is not legal (it has a meaningless minute part) and will become ' 00:00:00 '.
• The value of the HHMMSS format, which is assumed to be a meaningful time. For example, 101112 is understood as ' 10:11:12 '. The following format can also be understood: SS, MMSS, HHMMSS, Hhmmss.fraction. Please note that MySQL does not save fractions yet.
• The result returned by the function, whose value is appropriate for the time context, such as Current_time.
• A value that exceeds the time range but is valid is cropped to the nearest endpoint. For example, ' -850:00:00 ' and ' 850:00:00 ' are converted to ' -838:59:59 ' and ' 838:59:59 '.

3 Year Type

Year type in MySQL

MySQL retrieves and displays the year value in yyyy format. The range is 1901 to 2155.

You can specify year values in various formats:

• A four-bit string with a range of ' 1901 ' to ' 2155 '.
• A four-digit number, ranging from 1901 to 2155.
• A two-bit string with a range of ' 00 ' to ' 99 '. Values of ' 00 ' to ' 69 ' and ' 70 ' to ' 99 ' range are converted to a year value of 2000 to 2069 and 1970 to 1999 ranges.
• A two-bit integer that ranges from 1 to 99. Values from 1 to 69 and 70 to 99 range are converted to a year value of 2001 to 2069 and 1970 to 1999 ranges. Note that the two-bit integer range differs slightly from the two-bit string range because you cannot specify 0 directly 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, whose value is appropriate for the year context, such as now ().
• The illegal year value is converted to 0000.

4th-year compatibility issue

1. MySQL server uses the Unix time function, for timestamp value, can process the date until 2037. For date and datetime values, the acceptable dates are up to 9999.
2. All MySQL date functions are implemented in 1 source file sql/time.cc and are properly encoded to ensure 2000 security.
3. In MySQL 3.22 and later versions, the year column type can be saved in 1 bytes for 0 and 1901-2,155 years, and can be displayed using two-bit or four-digit numbers. All two-digit years are considered to be between 1970-2,069 years, which means that if you save the 01,mysql server in the year column, it will be treated as 2001.

MySQL in date and time type