MySQL: Query to get all rows from previous month

Mastering MySQL INTERVAL: Adding and Subtracting Time

When working with databases, manipulating dates and times is a common task. Whether you need to find records from the last 30 days, calculate a future deadline, or analyze data within specific timeframes, MySQL's INTERVAL keyword is your best friend.

The INTERVAL keyword is a powerful modifier used primarily with date and time functions like DATE_ADD(), DATE_SUB(), TIMESTAMPADD(), and TIMESTAMPDIFF(). It allows you to specify a duration or a period of time that you want to add to or subtract from a date or datetime value.

The Basics: DATE_ADD() and DATE_SUB() with INTERVAL

The most common use cases for INTERVAL are with the DATE_ADD() and DATE_SUB() functions.

• DATE_ADD(date, INTERVAL value unit): Adds the specified value and unit to the date.

• DATE_SUB(date, INTERVAL value unit): Subtracts the specified value and unit from the date.

Syntax:

SELECT DATE_ADD(date, INTERVAL value unit);
SELECT DATE_SUB(date, INTERVAL value unit);

• date: The starting date or datetime value. This can be a column, a literal date string, or a function like CURDATE() or NOW().

• value: A numeric expression specifying the amount of time.

• unit: A keyword indicating the unit of time (e.g., DAY, MONTH, YEAR, HOUR, MINUTE, SECOND).

Common INTERVAL Units

MySQL supports a wide range of units:

Unit Keyword	Description	Example value Format
MICROSECOND	Microseconds	10
SECOND	Seconds	30
MINUTE	Minutes	5
HOUR	Hours	2
DAY	Days	7
WEEK	Weeks (7 days)	2
MONTH	Months	1
QUARTER	Quarters (3 months)	1
YEAR	Years	1
MINUTE_SECOND	Minutes and seconds	'1:30'
HOUR_MINUTE	Hours and minutes	'2:45'
DAY_HOUR	Days and hours	'3 12'
YEAR_MONTH	Years and months	'1-6'
HOUR_SECOND	Hours, minutes, and seconds	'1:30:45'
DAY_MINUTE	Days, hours, and minutes	'1 08:30'
DAY_SECOND	Days, hours, minutes, and seconds	'1 02:03:04'
SECOND_MICROSECOND	Seconds and microseconds	'1.234567'
MINUTE_MICROSECOND	Minutes, seconds, and microseconds	'1:30.123456'
HOUR_MICROSECOND	Hours, minutes, seconds, and microseconds	'1:02:03.123456'
DAY_MICROSECOND	Days, hours, minutes, seconds, microseconds	'1 02:03:04.123456'

Note: For compound units (e.g., YEAR_MONTH), the value must be provided as a string in the specified format.

Practical Examples

Let's look at some real-world examples:

1. Adding/Subtracting Simple Intervals

-- Add 7 days to the current date
SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY);

-- Subtract 3 months from a specific date
SELECT DATE_SUB('2025-06-16', INTERVAL 3 MONTH);

-- Add 1 hour to the current timestamp
SELECT NOW(), DATE_ADD(NOW(), INTERVAL 1 HOUR);

-- Subtract 15 minutes
SELECT NOW(), DATE_SUB(NOW(), INTERVAL 15 MINUTE);

2. Using Compound Intervals

Compound intervals are useful when you need to specify multiple units at once.

-- Add 1 year and 6 months
SELECT '2023-01-15', DATE_ADD('2023-01-15', INTERVAL '1-6' YEAR_MONTH);

-- Subtract 5 days and 10 hours
SELECT '2025-06-16 10:00:00', DATE_SUB('2025-06-16 10:00:00', INTERVAL '5 10' DAY_HOUR);

-- Add 3 hours, 45 minutes, and 20 seconds
SELECT NOW(), DATE_ADD(NOW(), INTERVAL '3:45:20' HOUR_SECOND);

3. Dealing with Edge Cases (Month End)

MySQL's DATE_ADD() and DATE_SUB() are intelligent about month ends.

-- Adding 1 month to Jan 31st will result in Feb 28th/29th
SELECT '2024-01-31', DATE_ADD('2024-01-31', INTERVAL 1 MONTH); -- Results in '2024-02-29' (Leap Year)
SELECT '2025-01-31', DATE_ADD('2025-01-31', INTERVAL 1 MONTH); -- Results in '2025-02-28'

4. TIMESTAMPADD() and TIMESTAMPDIFF()

While DATE_ADD() and DATE_SUB() are convenient, TIMESTAMPADD() and TIMESTAMPDIFF() offer more explicit control over the units and are often preferred for clarity, especially when dealing with timestamps.

• TIMESTAMPADD(unit, interval, datetime_expr): Adds an integer interval to datetime_expr, where unit is the unit for the interval.

• TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2): Returns the integer difference between two datetime expressions in the specified unit.

Syntax:

SELECT TIMESTAMPADD(unit, value, date);
SELECT TIMEDIFF(unit, date1, date2);

Examples:

-- Add 20 minutes using TIMESTAMPADD
SELECT NOW(), TIMESTAMPADD(MINUTE, 20, NOW());

-- Find the difference in days between two dates
SELECT TIMESTAMPDIF(DAY, '2025-06-01', '2025-06-16'); -- Returns 15

-- Find the difference in months
SELECT TIMESTAMPDIF(MONTH, '2024-01-15', '2025-06-16'); -- Returns 17

SELECT *

FROM table

WHERE

YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)

AND

MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

Best Practices and Tips

• Clarity: Using INTERVAL with DATE_ADD()/DATE_SUB() often makes your SQL code more readable than manual date arithmetic.

• Performance: These functions are optimized for date and time calculations and generally perform well.

• Data Types: Be mindful of the data types. If you add/subtract from a DATE type, the result will be a DATE. If you use a DATETIME or TIMESTAMP, the result will maintain the time component.

• Validation: Always test your INTERVAL calculations, especially with edge cases like month ends or leap years, to ensure they produce the expected results.

Conclusion

The INTERVAL keyword is an indispensable tool in MySQL for handling date and time arithmetic. By mastering its use with functions like DATE_ADD(), DATE_SUB(), TIMESTAMPADD(), and TIMESTAMPDIFF(), you can write more efficient, readable, and robust queries for all your temporal data manipulation needs. Incorporate it into your SQL toolkit to simplify complex date calculations and enhance your database applications.