| 失效链接处理 |
|
mysql 5.7 官方详细文档 PDF 下载
本站整理下载：
提取码：arxl
相关截图：
主要内容：
11.2.1 Date and Time Data Type Syntax
The date and time data types for representing temporal values are DATE, TIME, DATETIME,
TIMESTAMP, and YEAR.
For the DATE and DATETIME range descriptions, “supported” means that although earlier values might
work, there is no guarantee.
MySQL permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to
microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the
syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the
fractional seconds precision. For example:
CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));
The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional
part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for
compatibility with previous MySQL versions.)
Any TIMESTAMP or DATETIME column in a table can have automatic initialization and updating
properties; see Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and
DATETIME”. • DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values
in 'YYYY-MM-DD' format, but permits assignment of values to DATE columns using either strings or
numbers.
• DATETIME[(fsp)]
A date and time combination. The supported range is '1000-01-01 00:00:00.000000' to
'9999-12-31 23:59:59.999999'. MySQL displays DATETIME values in 'YYYY-MM-DD
hh:mm:ss[.fraction]' format, but permits assignment of values to DATETIME columns using
either strings or numbers.
An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision.
A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
Automatic initialization and updating to the current date and time for DATETIME columns can be
specified using DEFAULT and ON UPDATE column definition clauses, as described in Section 11.2.6,
“Automatic Initialization and Updating for TIMESTAMP and DATETIME”. • TIMESTAMP[(fsp)]
A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19
03:14:07.999999' UTC. TIMESTAMP values are stored as the number of seconds since the
epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01
00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for
representing '0000-00-00 00:00:00', the “zero” TIMESTAMP value.
An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision.
A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
1541
Date and Time Data Type Syntax
The way the server handles TIMESTAMP definitions depends on the value of the
explicit_defaults_for_timestamp system variable (see Section 5.1.7, “Server System
Variables”).
If explicit_defaults_for_timestamp is enabled, there is no automatic assignment of
the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes to any
TIMESTAMP column. They must be included explicitly in the column definition. Also, any TIMESTAMP
not explicitly declared as NOT NULL permits NULL values.
If explicit_defaults_for_timestamp is disabled, the server handles TIMESTAMP as follows:
Unless specified otherwise, the first TIMESTAMP column in a table is defined to be automatically set
to the date and time of the most recent modification if not explicitly assigned a value. This makes
TIMESTAMP useful for recording the timestamp of an INSERT or UPDATE operation. You can also set
any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been
defined with the NULL attribute to permit NULL values.
Automatic initialization and updating to the current date and time can be specified using DEFAULT
CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definition clauses. By
default, the first TIMESTAMP column has these properties, as previously noted. However, any
TIMESTAMP column in a table can be defined to have these properties.
• TIME[(fsp)]
A time. The range is '-838:59:59.000000' to '838:59:59.000000'. MySQL displays TIME
values in 'hh:mm:ss[.fraction]' format, but permits assignment of values to TIME columns
using either strings or numbers.
An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision.
A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
• YEAR[(4)]
A year in 4-digit format. MySQL displays YEAR values in YYYY format, but permits assignment of
values to YEAR columns using either strings or numbers. Values display as 1901 to 2155, or 0000.
Note
The YEAR(2) data type is deprecated and support for it is removed in MySQL
5.7.5. To convert 2-digit YEAR(2) columns to 4-digit YEAR columns, see
Section 11.2.5, “2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR”.
For additional information about YEAR display format and interpretation of input values, see
Section 11.2.4, “The YEAR Type”.
The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values
to numbers, losing everything after the first nonnumeric character.) To work around this problem,
convert to numeric units, perform the aggregate operation, and convert back to a temporal value.
Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;
Note
The MySQL server can be run with the MAXDB SQL mode enabled. In this case,
TIMESTAMP is identical with DATETIME. If this mode is enabled at the time that
a table is created, TIMESTAMP columns are created as DATETIME columns. As
a result, such columns use DATETIME display format, have the same range of
values, and there is no automatic initialization or updating to the current date
and time. See Section 5.1.10, “Server SQL Modes”.
1542
The DATE, DATETIME, and TIMESTAMP Types
Note
As of MySQL 5.7.22, MAXDB is deprecated. It will be removed in a future version
of MySQL.
11.2.2 The DATE, DATETIME, and TIMESTAMP Types
The DATE, DATETIME, and TIMESTAMP types are related. This section describes their characteristics,
how they are similar, and how they differ. MySQL recognizes DATE, DATETIME, and TIMESTAMP
values in several formats, described in Section 9.1.3, “Date and Time Literals”. For the DATE and
DATETIME range descriptions, “supported” means that although earlier values might work, there is no
guarantee.
The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE
values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves
and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a
range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds
(6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP
column is stored rather than discarded. With the fractional part included, the format for these values
is 'YYYY-MM-DD hh:mm:ss[.fraction]', the range for DATETIME values is '1000-01-01
00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values
is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. The fractional
part should always be separated from the rest of the time by a decimal point; no other fractional
seconds delimiter is recognized. For information about fractional seconds support in MySQL, see
Section 11.2.7, “Fractional Seconds in Time Values”.
The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current
date and time. For more information, see Section 11.2.6, “Automatic Initialization and Updating for
TIMESTAMP and DATETIME”.
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from
UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By
default, the current time zone for each connection is the server's time. The time zone can be set on a
per-connection basis. As long as the time zone setting remains constant, you get back the same value
you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value,
the retrieved value is different from the value you stored. This occurs because the same time zone
was not used for conversion in both directions. The current time zone is available as the value of the
time_zone system variable. For more information, see Section 5.1.13, “MySQL Server Time Zone
Support”.
Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate
type ('0000-00-00' or '0000-00-00 00:00:00'), if the SQL mode permits this conversion. The
precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are
enabled; see Section 5.1.10, “Server SQL Modes”.
Be aware of certain properties of date value interpretation in MySQL:
• MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character
may be used as the delimiter between date parts or time parts. In some cases, this syntax can be
deceiving. For example, a value such as '10:11:12' might look like a time value because of the
:, but is interpreted as the year '2010-11-12' if used in date context. The value '10:45:15' is
converted to '0000-00-00' because '45' is not a valid month.
1543
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
