DATE_SUB() in SQL
Introduction
DATE_SUB() is a MySQL date function that subtracts a specified interval from a date or date-time value. It is used whenever an application needs to move backward in time: finding yesterday, calculating a reminder before a due date, establishing a retention cutoff, selecting recent activity, or comparing current results with an earlier reporting period.
SELECT DATE_SUB(
'2026-09-26',
INTERVAL 7 DAY
) AS earlier_date;
-- 2026-09-19
The mental model is simple: starting value minus typed interval equals an earlier temporal value. The production details are less simple. A calendar month is not always 30 days, the previous calendar day is not the same as the previous 24 hours, and a business-day deadline cannot be calculated by blindly subtracting weekdays. Reliable use begins by translating the business meaning into the correct temporal rule.
Basic Syntax
DATE_SUB(date_expression, INTERVAL value unit)
The first argument is a DATE, DATETIME, TIMESTAMP, or compatible expression. The value is the quantity to subtract, and the unit defines what that quantity means. MySQL needs both the INTERVAL keyword and a supported unit because the number seven alone does not say whether the requirement means days, weeks, months, hours, or seconds.
SELECT DATE_SUB(
'2026-09-26 18:30:00',
INTERVAL 5 DAY
) AS new_value;
-- 2026-09-21 18:30:00
Common Interval Units
| Unit | Meaning | Common use |
|---|---|---|
SECOND | Seconds | Request and token windows |
MINUTE | Minutes | Monitoring and recent activity |
HOUR | Hours | Operational dashboards |
DAY | Calendar days | Cutoffs and reminders |
WEEK | Seven-day groups | Recent reports |
MONTH | Calendar months | Billing comparisons |
QUARTER | Three-month groups | Review periods |
YEAR | Calendar years | Year-over-year analysis |
MySQL also supports compound units such as DAY_HOUR, DAY_MINUTE, and HOUR_SECOND. A single unit is easier to read when it is sufficient, while a compound interval can communicate a precise mixed duration without chaining several function calls.
Subtracting Days and Weeks
Day subtraction automatically crosses month and year boundaries. There is no need to count the remaining days in the current month or manually select the previous month.
SELECT DATE_SUB('2026-09-26', INTERVAL 10 DAY);
-- 2026-09-16
SELECT DATE_SUB('2027-01-03', INTERVAL 5 DAY);
-- 2026-12-29
SELECT DATE_SUB('2026-09-26', INTERVAL 2 WEEK);
-- 2026-09-12
A week means seven calendar days. It does not automatically exclude weekends, company holidays, or market closure dates.
Yesterday and Previous Calendar Dates
Subtracting one day from CURRENT_DATE produces yesterday's date according to the database session's calendar context.
SELECT DATE_SUB(
CURRENT_DATE,
INTERVAL 1 DAY
) AS yesterday;
This returns a DATE boundary. It is different from subtracting 24 hours from the current instant, especially when the application works across time zones or daylight-saving transitions.
Subtracting Months and Quarters
Month and quarter intervals perform calendar arithmetic. MySQL tries to preserve the day number, then adjusts when the target month does not contain that day.
SELECT DATE_SUB('2026-09-26', INTERVAL 1 MONTH);
-- 2026-08-26
SELECT DATE_SUB('2026-09-26', INTERVAL 3 MONTH);
-- 2026-06-26
SELECT DATE_SUB('2026-10-31', INTERVAL 1 QUARTER);
-- target is adjusted to a valid July date
Calendar quarters are useful for business reporting, but a rolling 90-day window is not necessarily the same as one calendar quarter. Choose the expression that matches the report definition.
Subtracting Years
Year intervals are appropriate for anniversaries and year-over-year boundaries because they follow the calendar rather than assuming that every year has 365 days.
SELECT DATE_SUB('2026-09-26', INTERVAL 1 YEAR);
-- 2025-09-26
Leap years matter. A fixed 365 DAY duration and 1 YEAR can lead to different dates when February 29 lies in the relevant period.
Subtracting Hours, Minutes, and Seconds
Time units work naturally with DATETIME and TIMESTAMP values and can cross date boundaries.
SELECT DATE_SUB('2026-09-26 18:30:00', INTERVAL 3 HOUR);
-- 2026-09-26 15:30:00
SELECT DATE_SUB('2026-09-26 00:15:00', INTERVAL 45 MINUTE);
-- 2026-09-25 23:30:00
SELECT DATE_SUB('2026-09-26 18:30:00', INTERVAL 30 SECOND);
-- 2026-09-26 18:29:30
These units are common in monitoring, authentication, API throttling, transaction analysis, and scheduled-job diagnostics.
Return-Type Behavior
The result type depends on the input expression and interval. Date-only operations can return a DATE, while adding or subtracting time components generally requires a date-time result. Applications should not assume that every result has the same precision or string representation.
Use the database driver to retrieve a temporal type rather than parsing a formatted string whenever possible. Explicitly cast when an interface contract requires a particular type.
DATE, DATETIME, and TIMESTAMP Inputs
A DATE represents a calendar date without a time. DATETIME stores a date and wall-clock value without automatic time-zone conversion. MySQL TIMESTAMP values are commonly converted between the session time zone and UTC storage. These distinctions can affect what "one hour ago" means when services use different session settings.
Document whether the application stores instants in UTC, local appointment times as DATETIME, or date-only business concepts. Correct arithmetic starts with a correct data model.
Using DATE_SUB() with CURRENT_DATE
SELECT DATE_SUB(
CURRENT_DATE,
INTERVAL 30 DAY
) AS cutoff_date;
CURRENT_DATE is useful when the rule is defined by calendar dates. A report of registrations since the cutoff date may include full calendar days, but its exact inclusion rule still needs an upper boundary.
Using DATE_SUB() with NOW()
SELECT DATE_SUB(
NOW(),
INTERVAL 2 HOUR
) AS two_hours_ago;
NOW() includes the current date and time, so this expression creates a rolling instant. It is appropriate for recent logs, requests, monitoring events, and transaction windows where time of day matters.
Last 24 Hours
A rolling 24-hour query compares an indexed event timestamp with one calculated boundary.
SELECT order_id, created_at, status
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
AND created_at < NOW()
ORDER BY created_at DESC;
The explicit upper bound makes the window clear and helps reproducible reporting when both boundaries are captured once. In a long transaction, repeated current-time expressions can also deserve careful handling according to DBMS semantics.
Last Seven Calendar Dates
"Last seven days" can mean several things: the previous 168 hours, today plus six earlier calendar dates, or seven completed dates before today. For today plus six earlier dates, build date boundaries deliberately.
SELECT order_id, order_date
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY)
AND order_date < DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY);
Writing the definition before writing SQL prevents off-by-one errors and disagreements between dashboards.
Previous Completed Day
Yesterday as a complete calendar range begins at yesterday's midnight and ends at today's midnight. A half-open interval is precise and avoids inventing an artificial end such as 23:59:59.
SELECT *
FROM api_logs
WHERE request_time >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
AND request_time < CURRENT_DATE;
Previous Calendar Month
Subtracting one month gives the corresponding date one month earlier; it does not return the entire previous calendar month. Calculate the first day of the current month, then use that boundary to derive the previous month.
SET @this_month = DATE_FORMAT(CURRENT_DATE, '%Y-%m-01');
SELECT *
FROM invoices
WHERE issued_at >= DATE_SUB(@this_month, INTERVAL 1 MONTH)
AND issued_at < @this_month;
In production code, calculate or bind stable typed boundaries rather than relying on display-oriented formatting if the surrounding system provides a clearer date-construction method.
Previous Calendar Year
A complete previous year also needs two boundaries rather than a single value one year ago.
SET @this_year = STR_TO_DATE(
CONCAT(YEAR(CURRENT_DATE), '-01-01'),
'%Y-%m-%d'
);
SELECT *
FROM sales
WHERE sold_at >= DATE_SUB(@this_year, INTERVAL 1 YEAR)
AND sold_at < @this_year;
This definition is stable for year-over-year reporting and does not mix a partial current year with a complete prior year.
Recent Login and API Activity
SELECT user_id, login_time
FROM login_history
WHERE login_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR);
SELECT request_id, status_code, request_time
FROM api_logs
WHERE request_time >= DATE_SUB(NOW(), INTERVAL 15 MINUTE);
Short rolling windows are useful for operations, but a high-volume service may also need partition pruning, a covering index, aggregation, sampling, or a dedicated observability platform.
Recent Failed Tests and Defects
SELECT test_name, executed_at, error_message
FROM test_execution
WHERE status = 'Failed'
AND executed_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY executed_at DESC;
SELECT defect_id, severity, created_at
FROM defects
WHERE created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);
These queries support engineering dashboards. Clarify whether the defect window is date-based while the execution window is instant-based; using both definitions is valid when each matches its business question.
Finding Records Older Than a Cutoff
To identify old rows, compare their timestamp with a boundary in the past.
SELECT log_id, created_at
FROM application_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
The strict less-than operator excludes a row exactly at the cutoff. If the policy states "90 days old or older," verify whether equality must be included and whether age is measured in elapsed hours or calendar dates.
Inactive Users and Customers
SELECT user_id, last_login_at
FROM users
WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
SELECT customer_id, last_order_at
FROM customers
WHERE last_order_at < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH);
NULL requires a separate policy. A customer who has never logged in or ordered does not satisfy an ordinary comparison because NULL means unknown or absent. Add an explicit IS NULL branch only if the business classifies that state as inactive.
Data Retention Cutoffs
Retention rules are one of the most consequential uses of DATE_SUB(). A cutoff can identify candidates for archival or deletion, but the SQL expression alone does not prove that deletion is legally or operationally safe.
SELECT log_id, created_at
FROM application_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
Confirm the authoritative time zone, legal hold rules, tenant policy, backup state, and whether the retention period is 90 elapsed days or three calendar months. Record the cutoff used by each cleanup run for auditability.
Safe Deletion Workflow
Never turn a new retention query directly into an unrestricted production DELETE. First count and sample candidates, verify the oldest and newest timestamps, test the execution plan, and process manageable batches.
SELECT COUNT(*) AS candidate_count,
MIN(created_at) AS oldest_row,
MAX(created_at) AS newest_candidate
FROM temporary_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
DELETE FROM temporary_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
ORDER BY created_at
LIMIT 10000;
Batch deletion can reduce lock duration, transaction-log growth, and replication pressure. The exact procedure depends on the storage engine and operational controls.
Moving a Stored Date Backward
DATE_SUB() can be used in UPDATE statements when the business operation intentionally moves a value earlier.
UPDATE appointments
SET appointment_date = DATE_SUB(
appointment_date,
INTERVAL 1 DAY
)
WHERE appointment_id = 101;
Use a restrictive WHERE clause, preview the target row, and retain an audit trail for consequential schedule changes. An accidental missing predicate can modify every row.
Reminder Dates Before Deadlines
A common forward-looking workflow starts with a known future deadline and subtracts a lead time.
SELECT invoice_id,
due_date,
DATE_SUB(due_date, INTERVAL 5 DAY) AS reminder_date
FROM invoices;
SELECT customer_id,
expiration_date,
DATE_SUB(expiration_date, INTERVAL 7 DAY) AS reminder_date
FROM subscriptions;
This produces a candidate date, not a complete notification policy. Delivery channels, user time zones, duplicate prevention, weekends, holidays, and consent rules may also apply.
Dynamic Intervals from Columns
Each row can carry its own lead time or retention duration.
SELECT task_id,
due_date,
reminder_days,
DATE_SUB(
due_date,
INTERVAL reminder_days DAY
) AS reminder_date
FROM tasks;
Validate interval values before storing them. Negative, unexpectedly large, or NULL durations may create dates in the wrong direction or produce NULL results.
Conditional Intervals with CASE
SELECT customer_name,
membership_type,
CASE
WHEN membership_type = 'Premium'
THEN DATE_SUB(expiration_date, INTERVAL 30 DAY)
ELSE DATE_SUB(expiration_date, INTERVAL 7 DAY)
END AS reminder_date
FROM memberships;
A CASE expression is reasonable for a small stable rule. Frequently changing policies are usually easier to govern in configuration or rule tables, where the effective date and reason for each change can be audited.
Month-End Behavior
Calendar subtraction must produce a valid date. When the destination month has fewer days, MySQL adjusts the result.
SELECT DATE_SUB('2026-03-31', INTERVAL 1 MONTH);
-- 2026-02-28
SELECT DATE_SUB('2028-03-31', INTERVAL 1 MONTH);
-- 2028-02-29
This behavior is valid, but it may not match every billing rule. Some systems want "last day of the previous month," while others want a fixed day such as the 28th. Encode and test the chosen policy explicitly.
Leap-Year Behavior
SELECT DATE_SUB('2028-03-01', INTERVAL 1 DAY);
-- 2028-02-29
Calendar-aware arithmetic handles leap days, but application tests should still include February 28, February 29, March 1, and year subtraction from a leap-day starting value.
Calendar Months Versus Fixed Days
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH);
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);
These expressions answer different questions. One month earlier follows the calendar; 30 days earlier subtracts a fixed number of dates. Subscription billing often uses months, while an operational retention policy may specify an exact number of days.
Calendar Years Versus 365 Days
The same distinction applies to years. INTERVAL 1 YEAR preserves calendar meaning, while INTERVAL 365 DAY is a fixed day count. Leap years can make them disagree. Requirements should say whether an anniversary or an elapsed duration is intended.
Last 24 Hours Versus Yesterday
| Requirement | Lower boundary | Upper boundary |
|---|---|---|
| Rolling last 24 hours | DATE_SUB(NOW(), INTERVAL 24 HOUR) | NOW() |
| Yesterday | DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) | CURRENT_DATE |
| Previous seven completed dates | DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) | CURRENT_DATE |
At 6:00 PM, a rolling day begins at 6:00 PM yesterday. Yesterday begins at midnight and ends at midnight today. Treating those windows as interchangeable is a common reporting defect.
Business Days Need a Calendar
DATE_SUB() understands calendar units but does not know company holidays, regional closures, emergency shutdowns, or working weekends. Business-day subtraction requires a maintained calendar table.
SELECT calendar_date
FROM business_calendar
WHERE calendar_date < :deadline
AND is_working_day = 1
ORDER BY calendar_date DESC
LIMIT 1 OFFSET 4;
This example finds the fifth previous working date under the table's rules. Production implementations should identify the relevant region and validate that the calendar is populated far enough into the past and future.
Time Zones and Daylight-Saving Changes
Subtracting one calendar day from a local wall-clock time is not always the same as subtracting 24 elapsed hours from an instant. Daylight-saving transitions can create 23-hour or 25-hour local days. Store instants consistently, often in UTC, and convert to the user's named time zone at a deliberate boundary.
A numeric offset such as -06:00 is not a complete time-zone policy because regional rules can change. Use named-zone support where local schedules matter and ensure the database time-zone tables are maintained.
Compound Intervals
MySQL supports compound units for mixed durations.
SELECT DATE_SUB(
'2026-09-26 18:30:00',
INTERVAL '2 03:15:00' DAY_SECOND
);
-- subtracts 2 days, 3 hours, 15 minutes
Compound syntax is compact but should be documented because it is less familiar than a single-unit interval. Validate input format carefully when values originate outside trusted code.
Negative Intervals
SELECT DATE_SUB(
CURRENT_DATE,
INTERVAL -7 DAY
);
Subtracting a negative interval moves forward, but it obscures intention. If the requirement adds seven days, use DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY). Clear direction is more maintainable than clever arithmetic.
DATE_SUB() with NULL
SELECT DATE_SUB(NULL, INTERVAL 7 DAY);
-- NULL
An unknown starting date produces an unknown result. Do not silently replace NULL with the current date unless that fallback is a documented rule. Use COALESCE only when the substitute has a valid business meaning.
Invalid Dates and SQL Modes
Invalid or loosely formatted date strings can trigger errors, warnings, or unexpected coercion depending on MySQL version and SQL mode. Bind proper temporal parameters, validate external input, and avoid depending on permissive parsing. Strict modes expose data defects earlier.
Overflow and Out-of-Range Results
A very large interval can move a result outside the supported range. Dynamic retention or reminder values should have sensible constraints. Test extreme values, inspect warnings, and decide whether application validation or database constraints own the boundary.
DATE_SUB() Versus Direct Interval Subtraction
SELECT CURRENT_DATE - INTERVAL 7 DAY;
SELECT DATE_SUB(
CURRENT_DATE,
INTERVAL 7 DAY
);
Both forms are valid in MySQL. The function form is explicit and often easier for beginners to recognize; direct arithmetic can be concise in a broader expression. Follow a consistent team convention.
DATE_SUB() Versus DATE_ADD()
DATE_SUB() moves backward by a positive interval, while DATE_ADD() moves forward. Use the function that states the real direction rather than combining one function with a negative interval.
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY); -- past
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY); -- future
DATE_SUB() Versus DATEDIFF()
DATE_SUB() takes one temporal value and an interval, then returns another temporal value. MySQL DATEDIFF() takes two dates and returns the number of day boundaries between them.
SELECT DATE_SUB('2026-09-26', INTERVAL 10 DAY);
-- 2026-09-16
SELECT DATEDIFF('2026-09-26', '2026-09-16');
-- 10
Portability Across Databases
DATE_SUB() is MySQL syntax. The concept exists across major relational databases, but the spelling differs.
| Database | Subtract seven days |
|---|---|
| MySQL | DATE_SUB(value, INTERVAL 7 DAY) |
| PostgreSQL | value - INTERVAL '7 days' |
| SQL Server | DATEADD(DAY, -7, value) |
| Oracle | value - 7 |
Oracle calendar-month subtraction commonly uses ADD_MONTHS(value, -1). Portability testing must include return types, month-end behavior, timestamp precision, and time-zone semantics, not only equivalent-looking syntax.
Index-Friendly Range Predicates
Apply the date function to the boundary, not to every indexed column value, when the query can be expressed that way.
-- Usually index-friendly
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
-- May make ordinary index access harder
WHERE DATE(created_at) >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
The first predicate leaves created_at bare for a range scan. Verify the real execution plan because selectivity, statistics, partitioning, data volume, and chosen columns also affect performance.
Stable Boundaries in Reports and Jobs
A report should ideally use one captured reference time so every query shares the same window. Calculate the boundary once in application code, a stored procedure variable, or an orchestration step, then bind it.
SET @report_end = NOW();
SET @report_start = DATE_SUB(@report_end, INTERVAL 30 DAY);
SELECT *
FROM transactions
WHERE transaction_time >= @report_start
AND transaction_time < @report_end;
This also records exactly which period the output represents.
Partition Pruning
Large event tables are often partitioned by date or timestamp ranges. A clear constant boundary can help MySQL eliminate irrelevant partitions. Functions wrapped around the partition key may make pruning less effective. Inspect the partition plan and keep retention boundaries aligned with the table design.
Prepared Statements and User Input
Bind date values and validated interval amounts rather than concatenating untrusted text into SQL. Interval units are grammar tokens and are not always bindable like ordinary values, so choose units from a strict application allowlist. Never accept arbitrary unit text from a request.
Fractional Seconds and Microsecond Intervals
Systems that capture high-volume events may store fractional seconds. MySQL supports microsecond intervals where the temporal type and column precision preserve them.
SELECT DATE_SUB(
'2026-09-26 18:30:00.500000',
INTERVAL 250000 MICROSECOND
);
-- 2026-09-26 18:30:00.250000
Do not assume that every driver, serialization format, or downstream warehouse retains the same precision. A range test should include events exactly at the microsecond boundary. Half-open ranges remain preferable because they avoid constructing a last representable instant that depends on column precision.
Late-Arriving Events and Safety Lookbacks
Event pipelines do not always receive records in event-time order. Network delays, mobile offline behavior, retries, and asynchronous queues can deliver an event after a reporting window has been processed. A job may deliberately subtract an additional lookback from its prior watermark.
SET @scan_start = DATE_SUB(
:previous_watermark,
INTERVAL 15 MINUTE
);
The overlap prevents missed late arrivals, but it means records can be processed more than once. Consumers therefore need an idempotency key, merge rule, or deduplication strategy. DATE_SUB defines the rescan boundary; it does not solve duplicate processing by itself.
Audit and Security Lookback Windows
Security reviews frequently search for activity before a suspicious event. The event time should be bound as a parameter rather than replaced by the current clock.
SELECT actor_id, action_name, occurred_at
FROM audit_event
WHERE occurred_at >= DATE_SUB(:incident_time, INTERVAL 2 HOUR)
AND occurred_at < :incident_time
ORDER BY occurred_at;
This query provides a deterministic two-hour history leading up to the incident. A complementary post-incident window can use DATE_ADD. Preserve the exact incident timestamp, time zone, and query parameters so an investigator can reproduce the result.
Effective-Dated Records
Price lists, policies, and employee assignments often have validity intervals. To reconstruct the state shortly before a known change, subtract a deliberately small interval only when the data model and precision make that rule safe.
SELECT policy_id, effective_from, effective_to
FROM policy_version
WHERE :reference_time >= effective_from
AND :reference_time < effective_to;
Usually it is better to query half-open validity ranges directly than to subtract one second from a boundary. Direct ranges avoid gaps caused by fractional precision and make adjacent versions meet exactly.
Recurring Schedule History
Subtracting a month repeatedly can drift after an end-of-month adjustment. Starting at March 31, subtracting one month produces February's last valid date; subtracting another month from that result may preserve the adjusted day rather than the original end-of-month intention.
Recurring schedules should retain an anchor rule such as "last calendar day" or "day 31, adjusted to month end." Generate each occurrence from that anchor instead of deriving every date from the previously adjusted occurrence. This keeps historical and future schedules aligned with the contract.
Archival Boundaries and Restoration
An archival process may copy old rows before deleting them from the active table. Use the same captured cutoff for selection, verification, copy, and deletion so rows cannot move between categories while the workflow runs.
SET @archive_cutoff = DATE_SUB(:run_time, INTERVAL 18 MONTH);
INSERT INTO order_archive
SELECT * FROM orders
WHERE created_at < @archive_cutoff;
After copying, verify row counts and checksums under an appropriate consistency model. Design and test the restoration path before depending on the archive. A correctly calculated cutoff cannot compensate for an archive that cannot be read or restored.
Testing DATE_SUB()
Temporal tests should use fixed values instead of the real clock. Include ordinary dates, month ends, year boundaries, leap days, NULL, invalid input, large intervals, daylight-saving transitions, and equality at every range boundary.
For rolling queries, insert rows immediately before, exactly at, and immediately after the lower and upper boundaries. Those tests reveal incorrect operators and precision assumptions. For cleanup jobs, test dry-run counts and confirm that protected rows remain untouched.
Common Mistakes
- Omitting the
INTERVALkeyword or interval unit. - Treating one month as exactly 30 days.
- Treating one year as exactly 365 days.
- Confusing yesterday with the rolling previous 24 hours.
- Using one date value when a complete reporting range needs two boundaries.
- Wrapping an indexed timestamp column in a function unnecessarily.
- Ignoring NULL values in inactivity reports.
- Using calendar subtraction for business-day rules.
- Deleting retention candidates without a preview, audit, or batch strategy.
- Assuming MySQL syntax is portable unchanged to other databases.
Best-Practice Checklist
- Define whether the requirement is a calendar period or elapsed duration.
- Use DATE for date-only concepts and an appropriate date-time type for instants.
- State the authoritative time zone for operational timestamps.
- Prefer half-open ranges with an inclusive lower and exclusive upper boundary.
- Keep indexed columns bare in search predicates when practical.
- Capture report and job boundaries once for consistent results.
- Use a maintained business calendar for working-day calculations.
- Validate dynamic intervals and allowlist interval units.
- Preview, count, batch, audit, and back up before destructive cleanup.
- Test month ends, leap years, time-zone transitions, NULL, and equality boundaries.
Interview Questions and Answers
What is DATE_SUB()? It is a MySQL function that subtracts a typed interval from a date or date-time and returns an earlier temporal value.
How do you get yesterday? Use DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY).
How do you get one hour ago? Use DATE_SUB(NOW(), INTERVAL 1 HOUR).
What is the difference between last month and the last 30 days? A month is a calendar interval; 30 days is a fixed day count. An entire previous calendar month requires lower and upper boundaries.
DATE_SUB versus DATEDIFF? DATE_SUB shifts one temporal value; DATEDIFF measures day distance between two dates.
How does NULL behave? A NULL starting value normally produces NULL.
Is DATE_SUB portable? No. PostgreSQL, SQL Server, and Oracle use different syntax.
How do you keep a recent-record query index-friendly? Compare the bare timestamp column with a calculated boundary and verify the execution plan.
Complete Retention Example
SET @run_time = NOW();
SET @cutoff = DATE_SUB(@run_time, INTERVAL 90 DAY);
SELECT COUNT(*) AS candidates,
MIN(created_at) AS oldest,
MAX(created_at) AS newest
FROM application_logs
WHERE created_at < @cutoff;
DELETE FROM application_logs
WHERE created_at < @cutoff
ORDER BY created_at
LIMIT 10000;
A real cleanup process repeats controlled batches, records the run time and cutoff, monitors replication and transaction pressure, honors legal holds, and verifies completion. The cutoff calculation is only one component of a safe retention design.
Key Takeaway
DATE_SUB() clearly expresses MySQL temporal subtraction: start with a date or date-time, subtract a typed interval, and receive an earlier value. Its most useful applications include rolling activity windows, historical comparisons, reminder dates, inactivity rules, and retention cutoffs.
RELIABLE DATE_SUB LOGIC = VALID START VALUE
- CORRECT INTERVAL UNIT
+ EXPLICIT RANGE BOUNDARIES
+ DEFINED CALENDAR POLICY
+ TIME-ZONE CONTEXT
+ INDEX-AWARE PREDICATES
+ BOUNDARY TESTS
The function is easy to write; the important work is defining what the interval means. Distinguish calendar dates from elapsed durations, previous periods from rolling windows, and calendar days from business days. Those decisions make historical reports, cleanup jobs, reminders, and operational dashboards accurate and maintainable.