Free tutorials to bookmark

MySQL provides several types and dates, including date and time types are DateTime, Date, TimesStamp, Time, and Year. Overview of these time and date types are as follows:

DATE

Date type is used without time when you only need the date value. The MySQL retrieval and display the Date value in the format of ‘yyyy-mm-dd’ format, and the range of support is ‘1000-01-01’ to ‘9999-12-31’.

Storage requirements: 3 bytes

TIME

Value range: “-838: 59: 59” to “838: 59: 59”

You can specify the Time value in multi -Chinese format:

• as a string of the ‘HH: MM: SS’ format. The syntax of “loose” is allowed-any punctuation symbol can be used as a separatist symbol of time part, for example, ’10: 11: 12 ‘and ’10 .11.12’ are equivalent.
• as a string of the ‘HHMMSS’ format without separators, if it is explained as a time. For example, ‘101112’ is understood as ’10: 11: 12 ‘, but’ 109712 ‘is illegal (it has a meaningful minute part) and becomes’ 00: 00: 00′.
• as a number in HHMMSS format, it can be interpreted as one time. For example, 101112 is understood as ’10: 11: 12 ‘.

Storage requirements: 3 bytes

DATATIME

DateTime type is used when you need to include the value of the date and time information at the same time. The MySQL retrieved and displayed by ‘yyyy-mm-dd HH: mm: ss’ format, the doteTime value, the range of support is’ 1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59’ ‘ Essence (“Support” means that although the earlier value may work, it cannot be guaranteed.)

Storage requirements: 8 bytes

TIMESTAMP[(M)]

TimesStamp column type provides a type, you can use it to automatically use the current date and time to mark Insert or Update. If you have multiple TimestAmp columns, only the first automatic update. The TimestAmp value can start from a certain time of 1970 to 2037, with an accuracy of one second, and its value is displayed as a number.

automatically update the first TimesStamp column under any conditions in the following conditions:

The

• column does not clearly specify in an insert or load data infile statement.

  The

• column did not clearly specify in a update statement and some other columns changed values. (Note that a UPDATE settings is listed as the value it already has, which will not cause the TimesStamp column to be updated, because if you set a list of its current value, MySQL ignores the change for efficiency.)
• You clearly set the timeStamp listed as null.

Storage requirements: 4 bytes

YEAR[(M)]

Value range: 1901 to 2155

YEAR type is an effective use of 1 byte type to represent the year. MySQL retrieves and shows the year value in YYYY format, and its range is 1901 to 2155. If you just want to save the date, then Year is more effective than other types such as Samllint.

You can specify the year value in multiple formats, you can use 4 as characters or 4 as string, of course, within the range of 1901 to 2155.

As a advantage of Year, you can specify a 2 -bit string of a string from ’00’ 99 ‘or a value of one from’ 00 ‘to’ 69 ‘and’ 70 ‘to’ 99 ‘. From 2000 to 2069 range and year to 1970 to 1999.

Storage requirements: 1 byte

mysql itself Y2K (year 2 kilo) is safe, but the input value that presented to MySQL may not be. Any input containing 2 years of year is vice, because the century is unknown. This value must be interpreted as a 4 -bit form, because mysql uses 4 -bit storage years. For DateTime, Date, TimesStamp, and Year types, mysql uses the following rules to explain the dual -meaning year value:

The annual value of

at 00-69 was converted to 2000-2069.

The annual value of

in the range of 70-99 was converted to 1970-1999.

Remember these rules only provide reasonable guessing the meaning of your data. If the inspiration rules used by MySQL do not generate the correct value, you should provide an input of 4 -bit annual values.