Date and Time Functions
929
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 | 0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+
In addition, the
SET TIMESTAMP
statement affects the value returned by
NOW()
[926]
but not
by
SYSDATE()
[928]
. This means that timestamp settings in the binary log have no effect on
invocations of
SYSDATE()
[928]
.
Because
SYSDATE()
[928]
can return different values even within the same statement, and
is not affected by
SET TIMESTAMP
, it is nondeterministic and therefore unsafe for replication.
If that is a problem, you can start the server with the
--sysdate-is-now
[423]
option to
cause
SYSDATE()
[928]
to be an alias for
NOW()
[926]
. The nondeterministic nature of
SYSDATE()
[928]
also means that indexes cannot be used for evaluating expressions that refer to
it.
•
TIME(expr)
[929]
Extracts the time part of the time or datetime expression
expr
and returns it as a string.
mysql>
SELECT TIME('2003-12-31 01:02:03');
-> '01:02:03'
mysql>
SELECT TIME('2003-12-31 01:02:03.000123');
-> '01:02:03.000123'
•
TIMEDIFF(expr1,expr2)
[929]
TIMEDIFF()
[929]
returns
expr1
–
expr2
expressed as a time value.
expr1
and
expr2
are time
or date-and-time expressions, but both must be of the same type.
The result returned by
TIMEDIFF()
is limited to the range allowed for
TIME
values. Alternatively,
you can use either of the functions
TIMESTAMPDIFF()
[930]
and
UNIX_TIMESTAMP()
[931]
,
both of which return integers.
mysql>
SELECT TIMEDIFF('2000:01:01 00:00:00',
->
'2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'
mysql>
SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
->
'2008-12-30 01:01:01.000002');
-> '46:58:57.999999'
•
TIMESTAMP(expr)
[929]
,
TIMESTAMP(expr1,expr2)
[929]
With a single argument, this function returns the date or datetime expression
expr
as a datetime
value. With two arguments, it adds the time expression
expr2
to the date or datetime expression
expr1
and returns the result as a datetime value.
mysql>
SELECT TIMESTAMP('2003-12-31');
-> '2003-12-31 00:00:00'
mysql>
SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
-> '2004-01-01 00:00:00'
•
TIMESTAMPADD(unit,interval,datetime_expr)
[929]
Adds the integer expression
interval
to the date or datetime expression
datetime_expr
. The
unit for
interval
is given by the
unit
argument, which should be one of the following values:
FRAC_SECOND
(microseconds),
SECOND
,
MINUTE
,
HOUR
,
DAY
,
WEEK
,
MONTH
,
QUARTER
, or
YEAR
.
Beginning with MySQL 5.0.60, it is possible to use
MICROSECOND
in place of
FRAC_SECOND
with this
function, and
FRAC_SECOND
is deprecated.
FRAC_SECOND
is removed in MySQL 5.5.
The
unit
value may be specified using one of keywords as shown, or with a prefix of
SQL_TSI_
.
For example,
DAY
and
SQL_TSI_DAY
both are legal.
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 ...
