Description

The DATEADD() function creates a new TIMESTAMP value by adding (or subtracting for negative values) the specified time interval from another TIMESTAMP value. The first argument specifies the time unit of the interval. The valid time unit keywords are:

The second argument is an integer value specifying the interval to add to the TIMESTAMP value. A positive interval moves the time ahead. A negative interval moves the time value backwards. The third argument specifies the TIMESTAMP value to which the interval is applied.

The DATEADD function takes into account leap years and the variable number of days in a month. Therefore, if the year of either the specified timestamp or the resulting timestamp is a leap year, the day is adjusted to its correct value. For example, DATEADD(YEAR, 1, ‘2008-02-29’) returns ‘2009-02-28’. Similarly, if the original timestamp is the last day of a month, then the resulting timestamp will be adjusted as necessary. For example, DATEADD(MONTH, 1, ‘2008-03-31’) returns ‘2008-04-30’.

Example

The following example uses the DATEADD() function to find all records where the TIMESTAMP column, incident, occurs within one day before a specified timestamp (entered as a POSIX time value).

SELECT incident, description FROM securityLog 
   WHERE DATEADD(DAY, 1, incident) > FROM_UNIXTIME(?) 
     AND incident < FROM_UNIXTIME(?) 
     ORDER BY incident, description;