DATE_ADD
Syntax
DATE_ADD(date,INTERVAL expr unit)
Contents
Description
Performs date arithmetic. The date argument specifies the
starting date or datetime value. expr is an expression specifying the
interval value to be added to the starting date. expr is a
string; it may start with a "-
" for negative intervals. unit is a
keyword indicating the units in which the expression should be interpreted. See Date and Time Units for a complete list of permitted units.
The result type of DATE_ADD()
is determined as follows:
- if the first argument is of the type
DATETIME
, the function returnsDATETIME
- if the first argument is
DATE
and the interval usesHOUR
or smaller units, the function returnsDATETIME
- if the first argument is
DATE
and the interval usesDAY
or larger units, the function returnsDATE
- similarly, if the first argument is
TIME
and the interval usesDAY
or smaller units the function returnsTIME
, if the interval uses anything larger, the function returnsDATETIME
- if the first argument isn't a temporal type, the function returns a string
Examples
SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND; +-------------------------------------------+ | '2008-12-31 23:59:59' + INTERVAL 1 SECOND | +-------------------------------------------+ | 2009-01-01 00:00:00 | +-------------------------------------------+
SELECT INTERVAL 1 DAY + '2008-12-31'; +-------------------------------+ | INTERVAL 1 DAY + '2008-12-31' | +-------------------------------+ | 2009-01-01 | +-------------------------------+
SELECT '2005-01-01' - INTERVAL 1 SECOND; +----------------------------------+ | '2005-01-01' - INTERVAL 1 SECOND | +----------------------------------+ | 2004-12-31 23:59:59 | +----------------------------------+
SELECT DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND); +----------------------------------------------------+ | DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND) | +----------------------------------------------------+ | 2001-01-01 00:00:00 | +----------------------------------------------------+
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY); +-------------------------------------------------+ | DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY) | +-------------------------------------------------+ | 2011-01-01 23:59:59 | +-------------------------------------------------+
SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); +---------------------------------------------------------------+ | DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) | +---------------------------------------------------------------+ | 2101-01-01 00:01:00 | +---------------------------------------------------------------+
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR); +------------------------------------------------------------+ | DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR) | +------------------------------------------------------------+ | 1899-12-30 14:00:00 | +------------------------------------------------------------+
SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND); +--------------------------------------------------------------------------------+ | DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND) | +--------------------------------------------------------------------------------+ | 1993-01-01 00:00:01.000001 | +--------------------------------------------------------------------------------+
See Also
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.