MySQL Learning Share-date Time type

Date Time Type

① If you want to use to denote the date and time, the datetime type is generally used;

② If you want to use the date type for the month and year date ;

③ if you want to represent a minute or seconds, use the time type generally;

④ if you only represent the year, you generally use year 5.5.27 year type has 2 bit and bit format these two representations, in 5.5.27 after version 2 Bit format year is no longer supported, year yyyy format display.

Year (2)deprecated my personal understanding is that the two-bit display width causes the type to be ambiguous, ambiguous (the displayed value is only the last two digits), so if you need to design the database using the Yeardata type, andMySQLversion is also less than5.5.27, it is recommended to Yeartype is defined asYear (4)to avoid problems, from5.5.27version starts when you create a table Yeartype is defined asYear (2), but actuallyMySQLwill be forced to define it asYear (4), see.

⑤ If you need to insert the current time of the system frequently or update the time to the current time of the system, the timestamp type is generally used , although timestamp and datetime display format and content, but because timestamp the range is less than datetime so that it only occupies 4 bytes, and a datetime occupy 8 a byte.

We created a table with the data type timestampfor the column D1, and by looking at the table definition you can see that MySQL automatically The default value of the timestamp type column is set to current_timestamp, which is the current time of the system, plus the on update Current_ Timestamp(when the row's data changes, the value of the column is updated to the current time of the system, as shown in the detailed results below).

we added column i1for t_dt_test table,int type display width is 2, width is not enough 0 Fill.

We first insert a data for the table, we can see that the inserted D1 value is 2016-10-22 15:25:31, and then we will i1 The value from the 1 Update to 2, and then see d1 found that the original deposit time was updated, the value of this time is 2016-10-22 15:26:27 , which is the effect of updating with the current timestamp of the system.

Insert a value exceeding the limit for a column of type timestamp, and find that MySQL will set its value to 0000-00-00 00:00:00, The first one inserts A value that exceeds the minimum lower limit of timestamp, and the second inserts a value that exceeds the maximum timestamp .

For data insertions of datetime types, the non-strict syntax is allowed.

① any punctuation can be used as a spacer between a date part or a time part, for example: "2016*10*24 11/11/11", "2016*10-24 [email PROTECTED]/11" Two kinds of non-strict format are can be inserted normally, see.

Although MySQL supports this non-strict syntax, but it is recommended to actually insert the data in a standard format (yyyy-mm-dd HH:MM:SS).

② for the month and day of the date part, if its value is less than ten, you can not specify a two-digit number, and for the time fraction of a second, if its value is less than ten, you can also not specify a two-digit number.

③yyyymmddhhmmss and YYMMDDHHMMSS are non-spacer strings or numbers, assuming they are meaningful for date types, can be interpreted as normal and inserted into the database, see .

Additional Knowledge points:

we know that if a column's data type is timestamp when the table is created , MySQL sets its default value to Current_ for the first timestamp in the table Timestamp, and the default value for the second timestamp in the column is set to ' 0000-00-00 00:00:00 ', as described below.

The number of digits following the t_dt_test_5628 table 5628 represents the MySQL version used, and the version here is 5.6.28.

What happens if we force the default value of two timestamp to current_timestamp When we create a table ?

We can see that the behavior is successful under the 5.6.28 version, so let's try it for a lower version.

It can be seen in the 5.1.57 version that this behavior is a failure due to the 4 - month - old in the 5.6.5 version of MySQL release , a maximum of one timestamp per table can be automatically initialized or updated the limit listed as current date and time ' has been lifted.

The following is excerpted from Release Notes for MySQL 5.6.5 release:

"Previously, at most one TIMESTAMP column per table could is automatically initialized or updated to the current date and Time. This restriction has been lifted. Any TIMESTAMP column definition can has any combination of the DEFAULT current_timestamp and on UPDATE current_timestamp Clau Ses. In addition, these clauses now can is used with DATETIME column definitions. "

To the effect:

Previously, each table could automatically initialize or update a TIMESTAMP column as the current date and time. This limit is now canceled. Any TIMESTAMP column definition can have DEFAULT current_timestamp and on UPDATE Current_ TIMESTAMP Any combination of these two clauses. In addition, these clauses can now be used in the definition of a DATETIME column. "

Functions related to date and time

1.now (),current_timestamp (),sysdate () These three functions can return the current system time, then what is the difference between them?

Now () and current_timestamp () The two functions are the same, they return the time when the SQL statement was executed, and Sysdate () returns the time when the function was executed.

2.If you want to take time to add or subtract, you can useDate_add (Date,interval expr unit)and thedate_sub (date,interval expr unit), the former is to make time increase, the latter is to make time decrease. Exprcan be a positive negative value,Unitcan be Year,Month,Week, Day,Hour,minute,Second,microsecond.

3.date_format (), used to format the printed date.

Additional Knowledge points:

added to time ,datetime,timestamp in the MySQL5.6.4 version three data types input microsecond support.

MySQL Learning Share-date Time type