Date and Time Functions
928
Range checking on the parts of date values is as described in
Section 11.1.5.1, “The
DATE
,
DATETIME
, and
TIMESTAMP
Types”
. This means, for example, that “zero” dates or dates with part
values of 0 are permitted unless the SQL mode is set to disallow such values.
mysql>
SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
-> '0000-00-00'
mysql>
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'
Note
You cannot use format
"%X%V"
to convert a year-week string to a date
because the combination of a year and week does not uniquely identify a year
and month if the week crosses a month boundary. To convert a year-week to
a date, you should also specify the weekday:
mysql>
SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
-> '2004-10-18'
•
SUBDATE(date,INTERVAL expr unit)
[928]
,
SUBDATE(expr,days)
[928]
When invoked with the
INTERVAL
form of the second argument,
SUBDATE()
[928]
is a synonym
for
DATE_SUB()
[922]
. For information on the
INTERVAL
unit
argument, see the discussion for
DATE_ADD()
[919]
.
mysql>
SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
-> '2007-12-02'
mysql>
SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
-> '2007-12-02'
The second form enables the use of an integer value for
days
. In such cases, it is interpreted as the
number of days to be subtracted from the date or datetime expression
expr
.
mysql>
SELECT SUBDATE('2008-01-02 12:00:00', 31);
-> '2007-12-02 12:00:00'
•
SUBTIME(expr1,expr2)
[928]
SUBTIME()
[928]
returns
expr1
–
expr2
expressed as a value in the same format as
expr1
.
expr1
is a time or datetime expression, and
expr2
is a time expression.
mysql>
SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
-> '2007-12-30 22:58:58.999997'
mysql>
SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
-> '-00:59:59.999999'
•
SYSDATE()
[928]
Returns the current date and time as a value in
'YYYY-MM-DD HH:MM:SS'
or
YYYYMMDDHHMMSS.uuuuuu
format, depending on whether the function is used in a string or numeric
context.
As of MySQL 5.0.12,
SYSDATE()
[928]
returns the time at which it executes. This differs from
the behavior for
NOW()
[926]
, which returns a constant time that indicates the time at which the
statement began to execute. (Within a stored function or trigger,
NOW()
[926]
returns the time at
which the function or triggering statement began to execute.)
mysql>
SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+
mysql>
SELECT SYSDATE(), SLEEP(2), SYSDATE();
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
