|
|
|
|
11.1.2. Overview of Date and Time Types
A summary of the temporal data types follows. For additional
information, see Section 11.3, “Date and Time Types”. Type
storage requirements are given in
Section 11.5, “Data Type Storage Requirements”.
The SUM() and AVG()
aggregate functions do not work with temporal values. (They
convert the values to numbers, which loses the part after the
first non-numeric character.) To work around this problem, you
can 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 ;
-
DATE
A date. The supported range is
'1000-01-01' to
'9999-12-31' . MySQL displays
DATE values in
'YYYY-MM-DD' format, but allows you to
assign values to DATE columns using
either strings or numbers.
-
DATETIME
A date and time combination. The supported range is
'1000-01-01 00:00:00' to
'9999-12-31 23:59:59' . MySQL displays
DATETIME values in 'YYYY-MM-DD
HH:MM:SS' format, but allows you to assign values
to DATETIME columns using either strings
or numbers.
-
TIMESTAMP[(M )]
A timestamp. The range is '1970-01-01
00:00:00' to partway through the year
2037 .
A TIMESTAMP column is useful for
recording the date and time of an INSERT
or UPDATE operation. By default, the
first TIMESTAMP column in a table is
automatically set to the date and time of the most recent
operation if you do not assign it a value yourself. You can
also set any TIMESTAMP column to the
current date and time by assigning it a
NULL value. Variations on automatic
initialization and update properties are described in
Section 11.3.1.1, “TIMESTAMP Properties as of MySQL 4.1”.
A TIMESTAMP value is returned as a string
in the format 'YYYY-MM-DD HH:MM:SS' whose
display width is fixed at 19 characters. To obtain the value
as a number, you should add +0 to the
timestamp column.
Note: The
TIMESTAMP format that was used prior to
MySQL 4.1 is not supported in MySQL 5.1; see
MySQL 3.23, 4.0, 4.1 Reference Manual for information
regarding the old format.
-
TIME
A time. The range is '-838:59:59' to
'838:59:59' . MySQL displays
TIME values in
'HH:MM:SS' format, but allows you to
assign values to TIME columns using
either strings or numbers.
-
YEAR[(2|4)]
A year in two-digit or four-digit format. The default is
four-digit format. In four-digit format, the allowable
values are 1901 to
2155 , and 0000 . In
two-digit format, the allowable values are
70 to 69 , representing
years from 1970 to 2069. MySQL displays
YEAR values in YYYY
format, but allows you to assign values to
YEAR columns using either strings or
numbers.
|
|
|