MariaDB: SUBDATE Function
This MariaDB tutorial explains how to use the MariaDB SUBDATE function with syntax and examples.
Description
The MariaDB SUBDATE function returns a date after which a certain time/date interval has been subtracted.
Syntax
The syntax for the SUBDATE function in MariaDB is:
SUBDATE( date, INTERVAL value unit )
OR
SUBDATE( date, days )
Parameters or Arguments
- date
- The date to which the interval should be subtracted.
- days
- The number of days to subtract from date (second syntax).
- value
- The value of the time/date interval that you wish to subtract. You can specify positive and negative values for this parameter (first syntax).
- unit
The unit type of the interval such as DAY, MONTH, MINUTE, HOUR, and so on. It can be one of the following (first syntax):
unit MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH
Note
- If you specify an interval value that is too short for the unit that you have specified, the SUBDATE function will assume that the left-most portion of the interval value was not provided.
- The SUBDATE function (first syntax only) is a synonym for the DATE_SUB function.
- Using the SUBDATE function with a negative value as a parameter is equivalent to using the ADDDATE function.
- See also the DATE_ADD, DATE_SUB, ADDDATE, ADDTIME , and SUBTIME functions.
Applies To
The SUBDATE function can be used in the following versions of MariaDB:
- MariaDB 10
Example
Let's look at some MariaDB SUBDATE function examples and explore how to use the SUBDATE function in MariaDB.
For example:
SELECT SUBDATE('2014-05-17 08:44:21.000001', INTERVAL 4 MICROSECOND); Result: '2014-05-17 08:44:20.999997' SELECT SUBDATE('2014-05-17 08:44:21', INTERVAL 20 SECOND); Result: '2014-05-17 08:44:01' SELECT SUBDATE('2014-05-17 08:44:21', INTERVAL 25 MINUTE); Result: '2014-05-17 08:19:21' SELECT SUBDATE('2014-05-17 08:44:21', INTERVAL 2 HOUR); Result: '2014-05-17 06:44:21' SELECT SUBDATE('2014-05-17', INTERVAL 10 DAY); Result: '2014-05-07' SELECT SUBDATE('2014-05-17', 10); Result: '2014-05-07' SELECT SUBDATE('2014-05-17', INTERVAL 12 WEEK); Result: '2014-02-22' SELECT SUBDATE('2014-05-17', INTERVAL 3 MONTH); Result: '2014-02-17' SELECT SUBDATE('2014-05-17', INTERVAL 3 QUARTER); Result: '2013-08-17' SELECT SUBDATE('2014-05-17', INTERVAL 5 YEAR); Result: '2009-05-17' SELECT SUBDATE('2014-05-17 08:44:21.000001', INTERVAL '12.000001' SECOND_MICROSECOND); Result: '2014-05-17 08:44:09.000000' SELECT SUBDATE('2014-05-17 08:44:21.000001', INTERVAL '3:12.000001' MINUTE_MICROSECOND); Result: '2014-05-17 08:41:09.000000' SELECT SUBDATE('2014-05-17 08:44:21', INTERVAL '3:12' MINUTE_SECOND); Result: '2014-05-17 08:41:09' SELECT SUBDATE('2014-05-17 08:44:21.000001', INTERVAL '1:03:12.000001' HOUR_MICROSECOND); Result: '2014-05-17 07:41:09.000000' SELECT SUBDATE('2014-05-17 08:44:21', INTERVAL '1:03:12' HOUR_SECOND); Result: '2014-05-17 07:41:09' SELECT SUBDATE('2014-05-17 08:44:21', INTERVAL '1:03' HOUR_MINUTE); Result: '2014-05-17 07:41:21' SELECT SUBDATE('2014-05-17 08:44:21.000001', INTERVAL '7 1:03:12.000001' DAY_MICROSECOND); Result: '2014-05-10 07:41:09.000000' SELECT SUBDATE('2014-05-17 08:44:21', INTERVAL '7 1:03:12' DAY_SECOND); Result: '2014-05-10 07:41:09' SELECT SUBDATE('2014-05-17 08:44:21', INTERVAL '7 1:03' DAY_MINUTE); Result: '2014-05-10 07:41:21' SELECT SUBDATE('2014-05-17 08:44:21', INTERVAL '7 1' DAY_HOUR); Result: '2014-05-10 07:44:21' SELECT SUBDATE('2014-05-17', INTERVAL '5-3' YEAR_MONTH); Result: '2009-02-17'
Advertisements