Dynamic period queries in MySQL for last week, last month, this year, etc.
📅 1. Last 7 Days
SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY;🗓️ 2. This Month
SELECT * FROM orders
WHERE MONTH(order_date) = MONTH(CURDATE())
AND YEAR(order_date) = YEAR(CURDATE());🗓️ 3. Last Month
SELECT * FROM orders
WHERE MONTH(order_date) = MONTH(CURDATE() - INTERVAL 1 MONTH)
AND YEAR(order_date) = YEAR(CURDATE() - INTERVAL 1 MONTH);📆 4. This Year
SELECT * FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE());📆 5. Last Year
SELECT * FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE() - INTERVAL 1 YEAR);📈 6. Last 30/90 Days
-- Last 30 days
SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY;
-- Last 90 days
SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 90 DAY;🗓️ 7. Between First and Last Day of a Month
-- Last month's range
SELECT * FROM orders
WHERE order_date BETWEEN
DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01') AND
LAST_DAY(CURDATE() - INTERVAL 1 MONTH);🔁 8. Week-Based (Monday to Sunday)
🟢 This Week
SELECT * FROM orders
WHERE YEARWEEK(order_date, 1) = YEARWEEK(CURDATE(), 1);🔵 Last Week
SELECT * FROM orders
WHERE YEARWEEK(order_date, 1) = YEARWEEK(CURDATE() - INTERVAL 1 WEEK, 1);
No comments yet! You be the first to comment.