How to use the MySQL date and time functions for addition and subtraction?

1 year ago
Ava Mitchell
2 minutes

MySQL offers multiple functions for adding and subtracting time, commonly used functions include:

  1. Perform addition operation on a date or time by adding an interval.
  1. Date: The date or time on which the operation will be performed.
  2. expr: The value of the addition operation can be either a positive or negative number.
  3. Unit: The unit for addition operations can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
  4. // The result of adding 1 month to ‘2022-01-01’ is ‘2022-02-01’
  1. Subtracts a specified interval of time from a given date or time, using the same parameters as the DATE_ADD function.
  1. SELECT DATE_SUB(‘2022-01-01’, INTERVAL 1 MONTH); // Result: 2021-12-01
  1. ADDDATE(date, INTERVAL expr unit): similar to DATE_ADD function, with similar usage.
  2. The SUBDATE function works the same as DATE_SUB and is used in a similar way.
  3. Add a specified amount of time to a date or time value, similar to the DATE_ADD function.
  1. Unit: The unit for addition operation can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
  2. expression: the value of the addition operation, which can be either a positive or negative number.
  3. datetime_expr: the date or time to be manipulated.
  4. SELECT DATE_ADD(‘2022-01-01’, INTERVAL 1 MONTH); // Result is 2022-02-01
  1. Calculate the difference between two dates or times using TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2).
  1. Unit: The unit of difference can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
  2. datetime_expr1: First date or time.
  3. datetime_expr2: the second date or time.
  4. SELECT TIMESTAMPDIFF in months between ‘2022-01-01’ and ‘2022-02-01’; // The result is 1.

The above are the commonly used date and time functions in MySQL. Choose the appropriate function based on your specific needs for operations.

bannerAds