MySQL's now 1 day

MySQL

SQL Functions

Date Manipulation

Database Management

Coding Tips

MySQL's now 1 day

Master System Design with Codemia

Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.

Start Practicing Learn More

Introduction

To add or subtract one day from the current date and time in MySQL, use NOW() + INTERVAL 1 DAY or DATE_ADD(NOW(), INTERVAL 1 DAY). To subtract, use NOW() - INTERVAL 1 DAY or DATE_SUB(NOW(), INTERVAL 1 DAY). Both syntaxes produce the same result. NOW() returns the current date and time as a DATETIME value, and INTERVAL arithmetic preserves the time component. For date-only results, use CURDATE() instead of NOW().

Adding One Day to NOW()

sql

1-- These are equivalent
2SELECT NOW() + INTERVAL 1 DAY;
3SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
4
5-- Example output (if now is 2025-03-02 14:30:00):
6-- 2025-03-03 14:30:00
7
8-- Add multiple days
9SELECT NOW() + INTERVAL 7 DAY;    -- One week from now
10SELECT NOW() + INTERVAL 30 DAY;   -- 30 days from now

Subtracting One Day from NOW()

sql

1-- These are equivalent
2SELECT NOW() - INTERVAL 1 DAY;
3SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);
4
5-- Example output (if now is 2025-03-02 14:30:00):
6-- 2025-03-01 14:30:00

Other Interval Units

sql

1-- Hours
2SELECT NOW() + INTERVAL 6 HOUR;
3
4-- Minutes
5SELECT NOW() - INTERVAL 30 MINUTE;
6
7-- Months
8SELECT NOW() + INTERVAL 1 MONTH;
9
10-- Years
11SELECT NOW() - INTERVAL 1 YEAR;
12
13-- Combined intervals
14SELECT NOW() + INTERVAL '1 6' DAY_HOUR;       -- 1 day and 6 hours
15SELECT NOW() + INTERVAL '2:30' HOUR_MINUTE;   -- 2 hours 30 minutes

Practical Use Cases

sql

1-- Find records from the last 24 hours
2SELECT * FROM orders
3WHERE created_at >= NOW() - INTERVAL 1 DAY;
4
5-- Find records from the last 7 days
6SELECT * FROM logs
7WHERE timestamp >= NOW() - INTERVAL 7 DAY;
8
9-- Set expiration date to tomorrow
10INSERT INTO sessions (user_id, expires_at)
11VALUES (1, NOW() + INTERVAL 1 DAY);
12
13-- Update records older than 30 days
14UPDATE notifications
15SET status = 'archived'
16WHERE created_at < NOW() - INTERVAL 30 DAY;
17
18-- Delete expired tokens
19DELETE FROM tokens
20WHERE expires_at < NOW();
21
22-- Records created today
23SELECT * FROM orders
24WHERE DATE(created_at) = CURDATE();
25
26-- Records created yesterday
27SELECT * FROM orders
28WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY;

NOW() vs CURDATE() vs CURRENT_TIMESTAMP

sql

1-- NOW() returns DATETIME (date + time)
2SELECT NOW();              -- 2025-03-02 14:30:00
3
4-- CURDATE() returns DATE only (no time)
5SELECT CURDATE();          -- 2025-03-02
6
7-- CURRENT_TIMESTAMP is an alias for NOW()
8SELECT CURRENT_TIMESTAMP;  -- 2025-03-02 14:30:00
9
10-- CURDATE() + INTERVAL gives a DATE result
11SELECT CURDATE() + INTERVAL 1 DAY;  -- 2025-03-03
12
13-- NOW() + INTERVAL gives a DATETIME result
14SELECT NOW() + INTERVAL 1 DAY;      -- 2025-03-03 14:30:00
15
16-- UTC versions
17SELECT UTC_TIMESTAMP();    -- Current UTC datetime
18SELECT UTC_DATE();         -- Current UTC date

Date Difference Calculations

sql

1-- Days between two dates
2SELECT DATEDIFF('2025-12-31', NOW());  -- Days until end of year
3
4-- Time difference
5SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_ago
6FROM orders
7WHERE id = 1;
8
9-- Records grouped by day
10SELECT DATE(created_at) AS day, COUNT(*) AS total
11FROM orders
12WHERE created_at >= NOW() - INTERVAL 30 DAY
13GROUP BY DATE(created_at)
14ORDER BY day;

Using in WHERE Clauses with Indexes

sql

1-- GOOD: This can use an index on created_at
2SELECT * FROM orders
3WHERE created_at >= NOW() - INTERVAL 1 DAY;
4
5-- BAD: Wrapping the column in a function prevents index usage
6SELECT * FROM orders
7WHERE DATE(created_at) >= CURDATE() - INTERVAL 1 DAY;
8
9-- GOOD alternative for "today's records" that uses the index
10SELECT * FROM orders
11WHERE created_at >= CURDATE()
12  AND created_at < CURDATE() + INTERVAL 1 DAY;

Common Pitfalls

Wrapping a column in DATE() prevents index usage: WHERE DATE(created_at) = CURDATE() forces a full table scan because MySQL cannot use the index on created_at through a function. Instead, use a range: WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY.
Confusing NOW() with CURDATE() in interval arithmetic: NOW() + INTERVAL 1 DAY returns a DATETIME with the time preserved. CURDATE() + INTERVAL 1 DAY returns a DATE with no time component. Using the wrong one in comparisons can include or exclude records near midnight boundaries.
Timezone inconsistencies between NOW() and UTC_TIMESTAMP(): NOW() returns the server's local time, which depends on the time_zone session variable. If your application stores timestamps in UTC but queries with NOW() in a non-UTC timezone, you get incorrect results. Use UTC_TIMESTAMP() or set the session timezone to UTC.
Using INTERVAL 1 MONTH near month boundaries: '2025-01-31' + INTERVAL 1 MONTH returns '2025-02-28', not March 3rd. MySQL clamps to the last valid day of the target month. Subtracting a month from the result does not return the original date: '2025-02-28' - INTERVAL 1 MONTH gives '2025-01-28', not January 31st.
NOW() returning the same value within a single statement: In a stored procedure or trigger, all references to NOW() within a single statement return the same value (statement start time). Use SYSDATE() if you need the actual clock time at each evaluation point, but be aware that SYSDATE() is not replication-safe.

Summary

Use NOW() + INTERVAL 1 DAY or DATE_ADD(NOW(), INTERVAL 1 DAY) to add one day
Use NOW() - INTERVAL 1 DAY or DATE_SUB(NOW(), INTERVAL 1 DAY) to subtract one day
Use CURDATE() for date-only calculations, NOW() when the time component matters
Avoid wrapping indexed columns in functions like DATE() — use range comparisons instead
Be aware of timezone differences between NOW() (local) and UTC_TIMESTAMP() (UTC)