Oracle 5.0, Reference Manual

Page: 937 / 3232

Date and Time Functions
917
Here is an example that uses date functions. The following query selects all rows with a
date_col
value from within the last 30 days:
mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
The query also selects rows with dates that lie in the future.
Functions that expect date values usually accept datetime values and ignore the time part. Functions
that expect time values usually accept datetime values and ignore the date part.
Functions that return the current date or time each are evaluated only once per query at the start of
query execution. This means that multiple references to a function such as
NOW()
[926] within a
single query always produce the same result. (For our purposes, a single query also includes a call to
a stored program (stored routine or trigger) and all subprograms called by that program.) This principle
also applies to
CURDATE()
[918] ,
CURTIME()
[918] ,
UTC_DATE()
[932] ,
UTC_TIME()
[932] ,
UTC_TIMESTAMP()
[932] , and to any of their synonyms.
The
CURRENT_TIMESTAMP()
[918] ,
CURRENT_TIME()
[918] ,
CURRENT_DATE()
[918] ,
and
FROM_UNIXTIME()
[923] functions return values in the connection's current time
zone, which is available as the value of the
time_zone
[500] system variable. In addition,
UNIX_TIMESTAMP()
[931] assumes that its argument is a datetime value in the current time zone.
See Section 10.6, “MySQL Server Time Zone Support” .
Some date functions can be used with “zero” dates or incomplete dates such as
'2001-11-00'
,
whereas others cannot. Functions that extract parts of dates typically work with incomplete dates and
thus can return 0 when you might otherwise expect a nonzero value. For example:
mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
-> 0, 0
Other functions expect complete dates and return
NULL
for incomplete dates. These include functions
that perform date arithmetic or that map parts of dates to names. For example:
mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);
-> NULL
mysql> SELECT DAYNAME('2006-05-00');
-> NULL
•
ADDDATE(date,INTERVAL expr unit)
[917] ,
ADDDATE(expr,days)
[917]
When invoked with the
INTERVAL
form of the second argument,
ADDDATE()
[917] is a
synonym for
DATE_ADD()
[919] . The related function
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_ADD('2008-01-02', INTERVAL 31 DAY);
-> '2008-02-02'
mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
-> '2008-02-02'
When invoked with the
days
form of the second argument, MySQL treats it as an integer number of
days to be added to
expr
.
mysql> SELECT ADDDATE('2008-01-02', 31);
-> '2008-02-02'
•
ADDTIME(expr1,expr2)
[917]
ADDTIME()
[917] adds
expr2
to
expr1
and returns the result.
expr1
is a time or datetime
expression, and
expr2
is a time expression.
mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
-> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
-> '03:00:01.999997'