Oracle 5.0, Справочное руководство

Страница: 858 / 3232

Date and Time Types
838
+-------------------------------------------+
Whether overflow occurs depends on the range of the operands, so another way to handle the
preceding expression is to use exact-value arithmetic because
DECIMAL
values have a larger range
than integers:
mysql> SELECT 9223372036854775807.0 + 1;
+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
| 9223372036854775808.0 |
+---------------------------+
Subtraction between integer values, where one is of type
UNSIGNED
, produces an unsigned result by
default. If the result would otherwise have been negative, it becomes the maximum integer value. If the
NO_UNSIGNED_SUBTRACTION
[537] SQL mode is enabled, the result is negative.
mysql> SET sql_mode = '';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| 18446744073709551615 |
+-------------------------+
mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| -1 |
+-------------------------+
If the result of such an operation is used to update an
UNSIGNED
integer column, the result is clipped
to the maximum value for the column type, or clipped to 0 if
NO_UNSIGNED_SUBTRACTION
[537] is
enabled. If strict SQL mode is enabled, an error occurs and the column remains unchanged.
11.1.5. Date and Time Types
The date and time types for representing temporal values are
DATE
,
TIME
,
DATETIME
,
TIMESTAMP
,
and
YEAR
. Each temporal type has a range of legal values, as well as a “zero” value that may be
used when you specify an illegal value that MySQL cannot represent. The
TIMESTAMP
type has
special automatic updating behavior, described later. For temporal type storage requirements, see
Section 11.2, “Data Type Storage Requirements” .
Keep in mind these general considerations when working with date and time types:
• MySQL retrieves values for a given date or time type in a standard output format, but it attempts to
interpret a variety of formats for input values that you supply (for example, when you specify a value
to be assigned to or compared to a date or time type). For a description of the permitted formats for
date and time types, see
Section 9.1.3, “Date and Time Literals” . It is expected that you supply legal
values. Unpredictable results may occur if you use values in other formats.
• Although MySQL tries to interpret values in several formats, date parts must always be given in year-
month-day order (for example,
'98-09-04'
), rather than in the month-day-year or day-month-year
orders commonly used elsewhere (for example,
'09-04-98'
,
'04-09-98'
).
• Dates containing two-digit year values are ambiguous because the century is unknown. MySQL
interprets two-digit year values using these rules:
• Year values in the range
70-99
are converted to
1970-1999
.
• Year values in the range
00-69
are converted to
2000-2069
.
See also Section 11.1.5.8, “Two-Digit Years in Dates” .