MySQL's datetime, date, and timestamp simple to understand

MySQL5 has three types of dates: datetime, date, and timestamp, except that date is used to indicate that a time is not taken, and the other two take seconds. The timestamp can also be accurate to milliseconds.

Date is the date of YYYY-MM-DD,

DATETIME is the day of the month Yyyy-mm-dd HH:MM:SS,

Timestamp is the day of the month and the second YYYY-MM-DD HH:MM:SS.

Then there is the difference,

DateTime Support Range: ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '

Timestamp support scope: same format, but not earlier than 1970 or later than 2037

DateTime Store 8 bytes, actual format, independent of time zone

Timestamp Storage 4 bytes, UTC format, time zone conversion

One: Timestamp

The properties of the timestamp column type are not fixed, depending on the MySQL version and the SQL mode that the server is running.

1. A table can have more than one timestamp column, but only one column will change to the current value of the database system based on data updates. Therefore, it makes no sense to have more than one timestamp column in a table, in fact a table only sets one timestamp column.

The default value for the 2.TIMESTAMP column is the CURRENT_TIMESTAMP constant value. When the record data changes, the TIMESTAMP column automatically sets its value to Current_timestamp.

Two: datetime

Use the datetime type when you need a value that contains both date and time information.

The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '

MySQL retrieves and displays the datetime values in the ' yyyy-mm-dd HH:MM:SS ' format.

Three: Date

Use the date type when you only need a DateTime value and do not need the time part

The scope of support is ' 1000-01-01 ' to ' 9999-12-31 '.

MySQL retrieves and displays the date value in the ' YYYY-MM-DD ' format.

Four, date format conversion

1. Date of string transfer

Select Str_to_date (' 2010-03-03 16:41:16 ', '%y-%m-%d%h:%i:%s ')

2. Date to String

Select Date_format (' 2010-03-03 16:41:16 ', '%y-%m-%d%h:%i:%s ')

Five, the date of the day of the month and the week, and other methods of obtaining

Select TIMESTAMP (' 2010-03-03 16:41:16 ');

Select DATE (' 2010-03-03 16:41:16 ');

Select year (' 2010-03-03 16:41:16 ');

Select MONTH (' 2010-03-03 16:41:16 ');

Select Day (' 2010-03-03 16:41:16 ');

Select time (' 2010-03-03 16:41:16 ');

Select Curtime ();

Select Curdate ();

Select Current_date;

Select Current_time;

Select Current_timestamp;

Vi. arithmetic operations of dates

The related functions are many and very simple to use, and it is recommended to check the MySQL reference manual for a look.

mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 day);

' 1999-01-02 '

mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 HOUR);

' 1999-01-01 01:00:00 '

mysql> SELECT date_add (' 1998-01-30 ', INTERVAL 1 MONTH);

' 1998-02-28 '

Seven, the date size comparison

Take the day when the number, holding the string when the date, oh, very simple.

and Update_time > ' 2010-03-02 16:48:41 '

and Update_time <= ' 2010-03-03 16:51:58 '

Viii. Description:

You can use any common format to specify DateTime, date, and timestamp values:

' Yyyy-mm-dd HH:MM:SS ' or ' yy-mm-dd HH:MM:SS ' format string. Allow "not strict" syntax: 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.

The string ' yyyy-mm-dd ' or ' yy-mm-dd ' format. It is also possible to use the "not strict" syntax. For example, ' 98-12-31 ', ' 98.12.31 ', ' 98/12/31 ' and ' [email protected]@31 ' are equivalent.

The ' yyyymmddhhmmss ' or ' YYMMDDHHMMSS ' format has no inter-cut string, assuming that the string is meaningful for the date type. For example, ' 19970523091528 ' and ' 970523091528 ' are interpreted as ' 1997-05-23 09:15:28 ', but ' 971122129015 ' is not legal (it has a meaningless minute part) and will become ' 0000-00-00 00:00:00 '.

The ' YYYYMMDD ' or ' YYMMDD ' format has no inter-cut string, assuming that the string is meaningful for the date type. For example, ' 19970523 ' and ' 970523 ' are interpreted as ' 1997-05-23 ', but ' 971332 ' is illegal (it has a meaningless month and day part) and will become ' 0000-00-00 '.

A number in the YYYYMMDDHHMMSS or YYMMDDHHMMSS format, assuming that the number is meaningful for the date type. For example, 19830905132800 and 830905132800 are interpreted as ' 1983-09-05 13:28:00 '.

A number in the YYYYMMDD or YYMMDD format, assuming that the number is meaningful for the date type. For example, 19830905 and 830905 are interpreted as ' 1983-09-05 '.

The invalid datetime, date, or timestamp value is converted to the "0" value of the corresponding type (' 0000-00-00 00:00:00 ', ' 0000-00-00 ', or 00000000000000).

For string values that include a date section cut, you do not need to specify a two-digit number if the day and month values are less than 10. ' 1979-6-9 ' is the same as ' 1979-06-09 '. Similarly, for string values that include a cut between time sections, you do not need to specify a two-digit number if the time, minute, and second values are less than 10. ' 1979-10-30 1:2:3 ' is the same as ' 1979-10-30 01:02:03 '.

=========================================================================

Be aware of certain flaws when specifying date values:

Specifying a value as a string allows for a non-strict format that may be spoofed. For example, the value ' 10:11:12 ' because the ': ' Inter-cut character may look like a time value, but is interpreted as the year ' 2010-11-12 ' if used for a date context value. The value ' 10:45:15 ' is converted to ' 0000-00-00 ' because ' 45 ' is not a legal month.

In non-strict mode, the MySQL server only checks the legality of the date: the range of the year, month, and Day is 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any dates that contain portions beyond these ranges are converted to ' 0000-00-00 '. Please note that you are still allowed to save illegal dates, such as ' 2002-04-31 '. To ensure that dates are valid when strict mode is not used, you should examine the application.

In strict mode, illegal dates are not accepted and are not converted.

This article is from the "My Java World" blog, so be sure to keep this source http://hanchaohan.blog.51cto.com/2996417/1751447

MySQL's datetime, date, and timestamp simple to understand