Understanding SUBDATE for Date Calculations in MariaDB

1. With INTERVAL
   

   This format is identical to DATE_SUB. You provide the date value you want to subtract from and the interval you want to subtract. The interval specifies the unit of time (e.g., days, months, years) and the amount to subtract.

   For example:

   SELECT SUBDATE(CURDATE(), INTERVAL 7 DAY);
   

   This query subtracts 7 days from the current date (CURDATE()) and returns the result.

2. With Integer for Days
   

   This is a simpler form that allows subtracting only days directly as an integer value.

   SELECT SUBDATE('2024-07-09', 5);
   

   Here, SUBDATE subtracts 5 days from the date '2024-07-09' and returns the new date.

• For subtracting only days, the integer format provides a simpler option.
• You can specify various time units like DAY, MONTH, YEAR, etc., in the interval.
• SUBDATE is interchangeable with DATE_SUB when using the interval format.

---

Subtracting Months

SELECT SUBDATE('2024-07-09', INTERVAL 3 MONTH);

This code subtracts 3 months from "2024-07-09" and returns the date four months prior (likely "2024-04-09").

Subtracting Years

SELECT SUBDATE('2024-07-09', INTERVAL 2 YEAR);

This code subtracts 2 years from "2024-07-09" and returns the date two years earlier (likely "2022-07-09").

Combining with CURDATE()

SELECT SUBDATE(CURDATE(), INTERVAL 1 WEEK);

This code subtracts 1 week from the current date (CURDATE()) and returns the date from last week.

Subtracting Days (Integer Format)

SELECT SUBDATE('2024-07-09', 10);  -- Subtracting 10 days

This code subtracts 10 days directly from "2024-07-09" using the integer format and returns the new date (likely "2024-06-29").

Finding Dates within a Range

SELECT * FROM orders
WHERE order_date >= SUBDATE(CURDATE(), INTERVAL 1 MONTH)
  AND order_date <= CURDATE();

This code retrieves orders placed within the last month. It subtracts 1 month from the current date and uses that as the starting point for the date range.

---

• DATE_SUB
  This function offers the same functionality as SUBDATE but is considered the standard way to subtract intervals from dates in MariaDB. It provides better compatibility and might be preferred for clarity in your code.

SELECT DATE_SUB(CURDATE(), INTERVAL 1 WEEK);

Additional Options (depending on your specific needs)

• Arithmetic with date literals
  In some cases, you might be able to achieve the same result by performing simple arithmetic with date literals. However, this approach can be less readable and less flexible for calculations involving intervals.

For example, to subtract 5 days from a date:

SELECT '2024-07-09' - INTERVAL 5 DAY;  -- Not recommended for general use

• Other date/time functions
  Depending on your specific goal, you might consider functions like DATE_ADD (to add an interval), DATEDIFF (to calculate the difference between two dates in days), or other date manipulation functions offered by MariaDB.