🔥 Real-World Scenario-Based Date-Time Questions in MySQL
🟢 1. Fetch today’s records
Question: Show all orders placed today.
SELECT * FROM orders
WHERE DATE(order_date) = CURDATE();🟢 2. Fetch orders made in the last 7 days
Question: Show records from the last 7 days including today.
SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY;🟢 3. Fetch orders between two dates (dynamic range)
Question: Show all orders between 2025-06-01 and 2025-06-13.
SELECT * FROM orders
WHERE order_date BETWEEN '2025-06-01' AND '2025-06-13';🟢 4. Fetch records for last month only
Question: Get all entries from the previous calendar month.
SELECT * FROM orders
WHERE MONTH(order_date) = MONTH(CURDATE() - INTERVAL 1 MONTH)
AND YEAR(order_date) = YEAR(CURDATE() - INTERVAL 1 MONTH);🟢 5. Group data month-wise
Question: Get total sales grouped by month.
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) as total_sales
FROM orders
GROUP BY month
ORDER BY month DESC;🟢 6. Count how many orders are placed each day for the past 7 days
SELECT DATE(order_date) AS order_day, COUNT(*) AS total_orders
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY
GROUP BY order_day;🟢 7. Calculate age from birthdate
Question: Calculate age of users from birthdate column.
SELECT name, birthdate, TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age
FROM users;🟢 8. Get records where time is between 9 AM and 6 PM
Question: Filter records made during working hours only.
SELECT * FROM attendance
WHERE TIME(in_time) BETWEEN '09:00:00' AND '18:00:00';🟢 9. Get records from this week (Monday to Sunday)
SELECT * FROM orders
WHERE YEARWEEK(order_date, 1) = YEARWEEK(CURDATE(), 1);🟢 10. Get records for the last 3 months (rolling period)
SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 3 MONTH;🟢 11. Show orders that are older than 6 months
SELECT * FROM orders
WHERE order_date < CURDATE() - INTERVAL 6 MONTH;🟢 12. Show the first and last order date of each customer
SELECT customer_id, MIN(order_date) AS first_order, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;🟢 13. Get orders placed on a weekend
SELECT * FROM orders
WHERE DAYOFWEEK(order_date) IN (1, 7); -- 1 = Sunday, 7 = Saturday🟢 14. Find difference in hours between two datetime fields
SELECT TIMESTAMPDIFF(HOUR, start_time, end_time) AS hours_spent
FROM tasks;🟢 15. Find all expired entries (where end_date is in past)
SELECT * FROM subscriptions
WHERE end_date < NOW();
No comments yet! You be the first to comment.