Date and Time Functions
920
• String otherwise.
To ensure that the result is
DATETIME
, you can use
CAST()
[948]
to convert the first argument to
DATETIME
.
MySQL permits any punctuation delimiter in the
expr
format. Those shown in the table are the
suggested delimiters. If the
date
argument is a
DATE
value and your calculations involve only
YEAR
,
MONTH
, and
DAY
parts (that is, no time parts), the result is a
DATE
value. Otherwise, the result is a
DATETIME
value.
Date arithmetic also can be performed using
INTERVAL
together with the
+
[906]
or
-
[906]
operator:
date
+ INTERVAL
expr
unit
date
- INTERVAL
expr
unit
INTERVAL expr unit
is permitted on either side of the
+
[906]
operator if the expression on
the other side is a date or datetime value. For the
-
[906]
operator,
INTERVAL expr unit
is
permitted only on the right side, because it makes no sense to subtract a date or datetime value from
an interval.
mysql>
SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
-> '2009-01-01 00:00:00'
mysql>
SELECT INTERVAL 1 DAY + '2008-12-31';
-> '2009-01-01'
mysql>
SELECT '2005-01-01' - INTERVAL 1 SECOND;
-> '2004-12-31 23:59:59'
mysql>
SELECT DATE_ADD('2000-12-31 23:59:59',
->
INTERVAL 1 SECOND);
-> '2001-01-01 00:00:00'
mysql>
SELECT DATE_ADD('2010-12-31 23:59:59',
->
INTERVAL 1 DAY);
-> '2011-01-01 23:59:59'
mysql>
SELECT DATE_ADD('2100-12-31 23:59:59',
->
INTERVAL '1:1' MINUTE_SECOND);
-> '2101-01-01 00:01:00'
mysql>
SELECT DATE_SUB('2005-01-01 00:00:00',
->
INTERVAL '1 1:1:1' DAY_SECOND);
-> '2004-12-30 22:58:59'
mysql>
SELECT DATE_ADD('1900-01-01 00:00:00',
->
INTERVAL '-1 10' DAY_HOUR);
-> '1899-12-30 14:00:00'
mysql>
SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql>
SELECT DATE_ADD('1992-12-31 23:59:59.000002',
->
INTERVAL '1.999999' SECOND_MICROSECOND);
-> '1993-01-01 00:00:01.000001'
If you specify an interval value that is too short (does not include all the interval parts that would
be expected from the
unit
keyword), MySQL assumes that you have left out the leftmost parts
of the interval value. For example, if you specify a
unit
of
DAY_SECOND
, the value of
expr
is
expected to have days, hours, minutes, and seconds parts. If you specify a value like
'1:10'
,
MySQL assumes that the days and hours parts are missing and the value represents minutes and
seconds. In other words,
'1:10' DAY_SECOND
is interpreted in such a way that it is equivalent to
'1:10' MINUTE_SECOND
. This is analogous to the way that MySQL interprets
TIME
values as
representing elapsed time rather than as a time of day.
Because
expr
is treated as a string, be careful if you specify a nonstring value with
INTERVAL
. For
example, with an interval specifier of
HOUR_MINUTE
,
6/4
evaluates to
1.5000
and is treated as 1
hour, 5000 minutes:
mysql>
SELECT 6/4;
-> 1.5000
mysql>
SELECT DATE_ADD('2009-01-01', INTERVAL 6/4 HOUR_MINUTE);
-> '2009-01-04 12:20:00'
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...