Real-World Date Queries in SQL
Introduction
Real-world date queries turn business language into precise temporal boundaries. Requirements such as today's orders, yesterday's sales, the previous calendar month, subscriptions expiring soon, overdue invoices, and failed tests from the last hour sound simple. Their SQL becomes reliable only after the developer decides whether the requirement describes a calendar date, an instant, a rolling duration, a reporting period, or an interval between two events.
Production tables commonly contain columns such as created_at, updated_at, order_date, joining_date, due_date, delivery_date, start_time, and end_time. Knowing individual date functions is useful, but the essential skill is combining data types, boundaries, NULL rules, indexes, and time-zone policy into one query whose meaning matches the business requirement.
The First Question: What Does the Requirement Mean?
Before writing SQL, translate the request into a timeline. "Today" is a calendar interval beginning at local midnight. "Last 24 hours" is a rolling interval beginning exactly 24 hours before the current instant. "Last seven calendar dates" includes today and six preceding dates, while "the last seven days" may mean seven complete days, seven rolling 24-hour periods, or a reporting convention defined by the business.
| Requirement | Typical meaning | Useful reference |
|---|---|---|
| Today | Current calendar date | CURRENT_DATE |
| Last 24 hours | Rolling elapsed period | NOW() |
| Current month | First day through next month boundary | Calendar boundaries |
| Expired | End date or instant is before the applicable clock | Business expiration rule |
| Happening now | Current instant lies inside an interval | start_time and end_time |
The same words can have different meanings in different systems. An insurance policy may expire at the end of a local calendar date, while an access token expires at an exact instant. Clarifying that distinction prevents an apparently valid query from being wrong at midnight or across regions.
The Half-Open Range Pattern
The most reusable timestamp-filtering pattern is a half-open interval: include the start boundary and exclude the next boundary.
WHERE event_time >= :start_time
AND event_time < :end_time
This represents [start_time, end_time). Adjacent periods meet without overlapping, and no artificial end-of-day value is required. It works with seconds, milliseconds, and microseconds, so it is safer than writing an upper boundary such as 23:59:59.
Today's Orders
When order_date is a DATE, equality expresses the requirement directly.
SELECT *
FROM orders
WHERE order_date = CURRENT_DATE;
When created_at includes time, use the day boundaries instead of equality or wrapping the indexed column in DATE().
SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE
AND created_at < CURRENT_DATE + INTERVAL 1 DAY;
This form includes midnight and every representable instant before tomorrow. It also gives the optimizer a direct range over created_at.
Yesterday and Tomorrow
Yesterday is the complete calendar date immediately before today, not the previous 24 hours.
SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL 1 DAY
AND created_at < CURRENT_DATE;
For tomorrow's appointments stored as DATE values, the query can compare directly with tomorrow's date.
SELECT *
FROM appointments
WHERE appointment_date = CURRENT_DATE + INTERVAL 1 DAY;
Reminder jobs should capture one reference time and use it throughout the run. Re-evaluating the clock after midnight can divide one batch across two different business dates.
Rolling Windows and Calendar Windows
A rolling seven-day window starts seven exact days before the current moment.
SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL 7 DAY;
Seven calendar dates, including today, begin at midnight six days ago and end at tomorrow's midnight.
SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL 6 DAY
AND created_at < CURRENT_DATE + INTERVAL 1 DAY;
Neither query is universally better. The first suits recent activity and monitoring; the second suits day-based reports. The choice must come from the requirement, not from whichever expression is easier to remember.
Last 30 Days and Older Records
-- Rolling previous 30 days
SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL 30 DAY;
-- Older than the rolling threshold
SELECT *
FROM orders
WHERE created_at < NOW() - INTERVAL 30 DAY;
Recent-record queries support dashboards, registrations, transactions, defects, and logins. Older-record queries support archiving, retention, cleanup, and inactivity detection. A cleanup job should also define whether the threshold is inclusive and whether legal holds or status conditions override age.
Current Month Boundaries
A current-month query should start on the first day of this month and stop at the first day of next month.
SELECT *
FROM orders
WHERE order_date >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
AND order_date < DATE_FORMAT(
CURRENT_DATE + INTERVAL 1 MONTH,
'%Y-%m-01'
);
An alternative using YEAR(order_date) and MONTH(order_date) is readable, but it applies functions to the filtered column. On a large indexed table, explicit boundaries usually provide a more direct index range.
Previous Month and Year Boundaries
SELECT *
FROM orders
WHERE order_date >= DATE_FORMAT(
CURRENT_DATE - INTERVAL 1 MONTH,
'%Y-%m-01'
)
AND order_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01');
This pattern handles the January-to-December transition automatically. The same boundary idea applies to a year.
SELECT *
FROM orders
WHERE order_date >= CONCAT(YEAR(CURRENT_DATE), '-01-01')
AND order_date < CONCAT(YEAR(CURRENT_DATE) + 1, '-01-01');
For a fixed year or month, pass concrete boundaries such as 2026-01-01 through 2027-01-01, or 2026-09-01 through 2026-10-01. Parameterized boundaries are easy to test and avoid repeatedly deriving constants for each row.
Why End-of-Day Literals Are Fragile
A predicate ending at 23:59:59 can miss values with fractional seconds, such as 23:59:59.500000. Increasing precision later can silently change results. The next-boundary form is independent of stored fractional precision.
WHERE created_at >= '2026-09-27'
AND created_at < '2026-09-28'
The same principle makes periods composable: one day's exclusive end is exactly the next day's inclusive start.
Future, Past, and Upcoming Appointments
-- Future appointments
SELECT *
FROM appointments
WHERE appointment_time > NOW()
ORDER BY appointment_time;
-- Past appointments
SELECT *
FROM appointments
WHERE appointment_time < NOW()
ORDER BY appointment_time DESC;
-- Upcoming rolling seven days
SELECT *
FROM appointments
WHERE appointment_time >= NOW()
AND appointment_time < NOW() + INTERVAL 7 DAY
ORDER BY appointment_time;
Decide whether an appointment exactly at the current instant belongs to the upcoming or active category. Consistent comparison operators prevent gaps and duplicate classification.
Subscription Expiration
For a date-based subscription whose final active date is included, active and expired states can be expressed as follows.
-- Expired
SELECT *
FROM subscriptions
WHERE expiration_date < CURRENT_DATE;
-- Active under inclusive expiration-date semantics
SELECT *
FROM subscriptions
WHERE start_date <= CURRENT_DATE
AND expiration_date >= CURRENT_DATE;
Real systems may also account for cancellation, grace periods, paused accounts, payment state, and exact expiration times. The date comparison must agree with those state rules.
Subscriptions Expiring Soon
SELECT customer_id,
expiration_date,
DATEDIFF(expiration_date, CURRENT_DATE) AS days_remaining
FROM subscriptions
WHERE expiration_date >= CURRENT_DATE
AND expiration_date < CURRENT_DATE + INTERVAL 8 DAY;
Under this definition, the result includes today through seven dates after today. A positive difference means days remain, zero means expiration is today, and a negative value means the date has passed. If "next seven days" excludes today, move the lower boundary to tomorrow.
Overdue Invoices
SELECT invoice_id,
due_date,
DATEDIFF(CURRENT_DATE, due_date) AS days_overdue
FROM invoices
WHERE due_date < CURRENT_DATE
AND payment_date IS NULL;
The NULL test is essential because a paid invoice should not remain overdue merely because its original due date passed. If partial payments are possible, filter by outstanding balance or payment status as well.
Classifying Invoice Status
SELECT invoice_id,
due_date,
CASE
WHEN payment_date IS NOT NULL THEN 'Paid'
WHEN due_date < CURRENT_DATE THEN 'Overdue'
WHEN due_date = CURRENT_DATE THEN 'Due Today'
ELSE 'Pending'
END AS invoice_status
FROM invoices;
CASE conditions are evaluated in order. Put the paid condition first so a paid invoice with an old due date is not classified as overdue.
Delivery Duration and Delays
SELECT order_id,
order_date,
delivery_date,
DATEDIFF(delivery_date, order_date) AS delivery_days
FROM orders
WHERE delivery_date IS NOT NULL;
DATEDIFF() measures calendar-date differences. It does not calculate business days or exact hours. A delayed-delivery report may filter completed deliveries by duration and pending deliveries by a direct threshold.
SELECT *
FROM orders
WHERE status = 'Pending'
AND order_date < CURRENT_DATE - INTERVAL 7 DAY;
The direct comparison keeps the date column available for range access and states the cutoff more clearly than calculating a difference for every row.
Employee Experience and Anniversaries
SELECT employee_name,
joining_date,
TIMESTAMPDIFF(
YEAR,
joining_date,
CURRENT_DATE
) AS experience_years
FROM employees;
TIMESTAMPDIFF(YEAR, ...) accounts for whether the anniversary has occurred. Dividing a day count by 365 fails around leap years and before the anniversary date.
SELECT *
FROM employees
WHERE MONTH(joining_date) = MONTH(CURRENT_DATE)
AND DAY(joining_date) = DAY(CURRENT_DATE);
Anniversary and birthday matching intentionally ignores the year. Organizations must define how February 29 anniversaries are handled in non-leap years.
Recent and Inactive Users
-- Rolling previous 24 hours
SELECT *
FROM users
WHERE registered_at >= NOW() - INTERVAL 24 HOUR;
-- Registered during today's calendar date
SELECT *
FROM users
WHERE registered_at >= CURRENT_DATE
AND registered_at < CURRENT_DATE + INTERVAL 1 DAY;
-- Never logged in or inactive for 90 days
SELECT *
FROM users
WHERE last_login IS NULL
OR last_login < NOW() - INTERVAL 90 DAY;
The first two queries demonstrate why labels matter: the last 24 hours and today are different sets except at midnight. NULL must be tested separately because comparison with NULL does not evaluate as true.
Daily Sales and Order Counts
SELECT DATE(created_at) AS sales_date,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_sales
FROM orders
WHERE created_at >= :report_start
AND created_at < :report_end
GROUP BY DATE(created_at)
ORDER BY sales_date;
Functions in the SELECT and GROUP BY clauses are appropriate for forming report buckets. The WHERE clause still uses raw timestamp boundaries to limit the indexed scan. A complete dashboard may join a calendar table so dates with zero orders appear explicitly.
Monthly, Yearly, and Quarterly Reports
SELECT YEAR(order_date) AS sales_year,
QUARTER(order_date) AS sales_quarter,
COUNT(*) AS order_count,
SUM(total_amount) AS total_sales
FROM orders
WHERE order_date >= :report_start
AND order_date < :report_end
GROUP BY YEAR(order_date), QUARTER(order_date)
ORDER BY sales_year, sales_quarter;
For monthly reports, group by both year and month or by a year-month key. Grouping only by month name combines January from every year. Stable numeric period keys should drive ordering, while formatted month names can be separate display labels.
Finding the Highest Sales Date
SELECT DATE(order_date) AS sales_date,
SUM(total_amount) AS total_sales
FROM orders
WHERE order_date >= :report_start
AND order_date < :report_end
GROUP BY DATE(order_date)
ORDER BY total_sales DESC, sales_date
LIMIT 1;
The report range is important. Without it, "highest sales date" means the entire retained history. A secondary sort makes the selected row deterministic when multiple dates have equal sales.
Weekend and Business-Hour Queries
-- MySQL: Sunday = 1 and Saturday = 7
SELECT *
FROM orders
WHERE DAYOFWEEK(order_date) IN (1, 7);
-- Local business hours: 09:00 through before 17:00
SELECT *
FROM orders
WHERE TIME(created_at) >= '09:00:00'
AND TIME(created_at) < '17:00:00';
Day numbering varies by DBMS and function. Business-hour logic also requires a business time zone and holiday policy. If timestamps are stored in UTC, convert to the relevant local zone before extracting weekday or time, or precompute correct UTC boundaries for each local date.
Latest, Oldest, First, and Last
-- Latest order
SELECT *
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 1;
-- History boundaries
SELECT MIN(order_date) AS first_order,
MAX(order_date) AS latest_order,
DATEDIFF(MAX(order_date), MIN(order_date)) AS history_days
FROM orders;
A stable identifier resolves ties when several rows share one timestamp. For per-customer latest orders, use a window function such as ROW_NUMBER() partitioned by customer rather than issuing one query per customer.
Customers Without Recent Orders
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= CURRENT_DATE - INTERVAL 90 DAY
);
NOT EXISTS expresses absence directly and handles NULL values more predictably than many NOT IN constructions. An index beginning with customer_id and including the date can support the correlated lookup.
Testing and Defect-Aging Queries
SELECT defect_id,
severity,
created_at,
DATEDIFF(CURRENT_DATE, DATE(created_at)) AS age_days
FROM defects
WHERE status <> 'Closed'
AND created_at < NOW() - INTERVAL 7 DAY;
Defect age may mean calendar days, elapsed hours, or working days under a service-level agreement. The displayed age can use a function, while the filtering cutoff should usually compare the stored column directly.
SELECT AVG(DATEDIFF(resolved_date, created_date))
AS average_resolution_days
FROM defects
WHERE resolved_date IS NOT NULL;
An average can hide outliers and open defects, so production quality reporting often includes median, percentiles, severity, and current unresolved age.
Operational Monitoring Windows
SELECT DATE_FORMAT(request_time, '%Y-%m-%d %H:00') AS error_hour,
COUNT(*) AS error_count
FROM api_logs
WHERE status_code >= 500
AND request_time >= NOW() - INTERVAL 24 HOUR
GROUP BY DATE_FORMAT(request_time, '%Y-%m-%d %H:00')
ORDER BY error_hour;
A monitoring query should state whether its clock is UTC or local. UTC buckets avoid repeated or missing local hours during daylight-saving transitions, while local buckets can be more intuitive for business operations. Label the zone in charts and alerts.
Data-Quality Date Checks
SELECT *
FROM orders
WHERE delivery_date < order_date;
SELECT *
FROM projects
WHERE end_date < start_date;
SELECT *
FROM records
WHERE updated_at < created_at;
These queries expose impossible or suspicious relationships. Some apparent anomalies can be legitimate because of migration, clock skew, correction workflows, or different time zones, so investigate semantics before deleting data. Database CHECK constraints can prevent many invalid combinations at write time.
Active and Completed Projects
SELECT project_id,
DATEDIFF(
COALESCE(end_date, CURRENT_DATE),
start_date
) AS duration_days
FROM projects;
COALESCE() lets an active project use the current date while completed projects use their actual end date. The result is a calendar-day measure. If exact elapsed time matters, use timestamp-aware calculations and state the required unit.
Events Happening Now
SELECT *
FROM events
WHERE start_time <= NOW()
AND end_time > NOW();
This treats an event as active from its inclusive start through its exclusive end. At the exact end instant, the event is complete. The convention allows one event to end precisely when another begins without both being active.
Detecting Overlapping Bookings
Two half-open intervals overlap when the existing start is before the requested end and the existing end is after the requested start.
SELECT *
FROM bookings
WHERE start_time < :requested_end
AND end_time > :requested_start;
This compact rule handles containment and partial overlap. It also allows back-to-back bookings because an existing booking ending at the requested start does not overlap.
Finding Available Resources
SELECT r.*
FROM rooms r
WHERE NOT EXISTS (
SELECT 1
FROM bookings b
WHERE b.room_id = r.room_id
AND b.start_time < :requested_end
AND b.end_time > :requested_start
);
This pattern works for rooms, equipment, appointments, rentals, and leave schedules. The final booking operation still needs transaction and concurrency protection; a prior availability SELECT alone cannot prevent two sessions from reserving the same slot simultaneously.
Sargable Date Filtering
A predicate is commonly called sargable when the optimizer can use it as a direct search argument. Compare an indexed date column with computed constants rather than transforming every stored value.
-- Less index-friendly in many plans
WHERE DATE(created_at) = '2026-09-27'
-- Direct range over the column
WHERE created_at >= '2026-09-27'
AND created_at < '2026-09-28'
Likewise, replace YEAR(order_date) = 2026 with the 2026 start and 2027 start boundaries. Confirm the actual plan with EXPLAIN; index design, statistics, selectivity, and DBMS capabilities still influence execution.
Functions Are Appropriate in Output
Avoiding functions in an indexed filter does not mean avoiding date functions everywhere. Formatting, grouping, and duration calculations belong naturally in SELECT when they do not control access to a large table.
SELECT order_id,
order_date,
DATE_FORMAT(order_date, '%M %d, %Y') AS display_date
FROM orders
WHERE order_date >= :start_date
AND order_date < :end_date
ORDER BY order_date;
The query retains a typed value for sorting and a separate string for presentation.
Time-Zone-Aware Local Days
If instants are stored in UTC but a report means "orders placed today in Chicago," UTC midnight is not the correct boundary. Convert Chicago midnight at the start and end of the requested date to UTC, then filter the stored UTC column.
SET @start_utc = CONVERT_TZ(
'2026-09-27 00:00:00',
'America/Chicago',
'UTC'
);
SET @end_utc = CONVERT_TZ(
'2026-09-28 00:00:00',
'America/Chicago',
'UTC'
);
SELECT *
FROM orders
WHERE created_at_utc >= @start_utc
AND created_at_utc < @end_utc;
Local days can be 23, 24, or 25 hours around daylight-saving changes. Convert both local boundaries independently rather than assuming that every local day lasts exactly 24 hours.
NULL Date Handling
NULL can mean unknown, not started, not delivered, not paid, or still active. Those meanings are different, so document each column. Use IS NULL and IS NOT NULL, never equality with NULL.
SELECT *
FROM orders
WHERE delivery_date IS NULL;
SELECT *
FROM employees
WHERE termination_date IS NULL;
Do not replace every missing date with an arbitrary sentinel such as 9999-12-31. Sentinels complicate constraints, reports, and integrations and can exceed supported ranges in downstream tools.
A Real-World E-Commerce Query
SELECT o.order_id,
o.customer_id,
o.created_at,
o.status,
o.total_amount,
DATEDIFF(CURRENT_DATE, DATE(o.created_at)) AS age_days
FROM orders o
WHERE o.status IN ('Pending', 'Processing')
AND o.created_at < NOW() - INTERVAL 48 HOUR
ORDER BY o.created_at, o.order_id;
This finds unresolved orders older than 48 elapsed hours and provides a calendar-age label for operations. A production rule might exclude weekends, wait for payment settlement, or use a warehouse-specific service level.
A Real-World Banking Query
SELECT account_id,
COUNT(*) AS transaction_count,
SUM(amount) AS total_amount
FROM transactions
WHERE posted_at >= :business_day_start_utc
AND posted_at < :business_day_end_utc
AND status = 'POSTED'
GROUP BY account_id;
Financial reporting should use governed business-day boundaries rather than whichever date the database server currently observes. The report should record its zone, cutoff policy, and as-of instant for auditability.
A Real-World Subscription Query
SELECT subscription_id,
customer_id,
expiration_date,
DATEDIFF(expiration_date, CURRENT_DATE) AS days_remaining
FROM subscriptions
WHERE status = 'ACTIVE'
AND auto_renew = 0
AND expiration_date >= CURRENT_DATE
AND expiration_date < CURRENT_DATE + INTERVAL 8 DAY
ORDER BY expiration_date, subscription_id;
The query supports reminder delivery while avoiding already expired, inactive, or automatically renewing subscriptions. A reminder history table should prevent repeated messages if the job runs more than once.
A Real-World Test Execution Query
SELECT suite_name,
COUNT(*) AS failed_runs,
MAX(executed_at) AS latest_failure
FROM test_execution
WHERE status = 'FAILED'
AND executed_at >= NOW() - INTERVAL 24 HOUR
GROUP BY suite_name
ORDER BY failed_runs DESC, suite_name;
This rolling window is suitable for operational triage. A daily quality report would instead use calendar boundaries in the team's reporting zone.
Common Mistakes
- Using equality to compare a timestamp with a date literal.
- Using
BETWEENwith a hand-written end-of-day timestamp. - Confusing today with the last 24 hours.
- Confusing the last 30 days with the previous calendar month.
- Applying DATE, YEAR, or formatting functions to indexed filter columns unnecessarily.
- Ignoring NULL states such as unpaid, undelivered, or still active.
- Using calendar-day functions for an elapsed-hour requirement.
- Grouping by month without including the year.
- Ignoring the business time zone for local-day reports.
- Assuming every local day is exactly 24 hours.
- Calculating availability without transaction-level concurrency protection.
- Calling the current clock repeatedly when one consistent report time is required.
Testing Date Queries
Automated tests should use a fixed clock and deliberate boundary values. Test exactly at midnight, one unit before and after each boundary, month and year transitions, leap day, fractional seconds, NULL values, daylight-saving transitions, and equal start/end values. Include records that fall exactly on the exclusive end to prove they are excluded.
Test the business vocabulary as well as SQL syntax. A rolling window and a calendar window may both return plausible data in the middle of a day, yet differ substantially near midnight. Use representative zones and verify generated execution plans for high-volume queries.
Use One Stable Reference Time
A multi-step report can become internally inconsistent if each statement obtains a new current time. Imagine a job that starts just before midnight: its first query may classify a record under Monday while a later query classifies the same record under Tuesday. Capture the report instant once in the application or SQL session and derive every boundary from it.
SET @report_now = NOW();
SET @report_date = DATE(@report_now);
SELECT *
FROM orders
WHERE created_at >= @report_date
AND created_at < @report_date + INTERVAL 1 DAY;
Persist the reference time in exported report metadata. This makes the result reproducible and explains why records created after the cutoff are absent even when someone opens the report later.
Calendar Tables for Business Dates
SQL date arithmetic alone cannot reliably answer requirements involving business days, public holidays, fiscal periods, retail weeks, payroll cycles, or organization-specific shutdowns. A calendar table stores one row per date with attributes such as weekday, holiday status, fiscal year, fiscal quarter, and next business date.
SELECT c.calendar_date,
COALESCE(SUM(o.total_amount), 0) AS total_sales
FROM business_calendar c
LEFT JOIN orders o
ON o.created_at >= c.day_start_utc
AND o.created_at < c.next_day_start_utc
WHERE c.calendar_date >= :start_date
AND c.calendar_date < :end_date
GROUP BY c.calendar_date
ORDER BY c.calendar_date;
The calendar supplies rows for dates with no orders and can hold UTC boundaries for a governed business zone. It also centralizes holiday and fiscal definitions instead of duplicating CASE expressions across reports.
Index Design for Date Workloads
An index on a temporal column helps only when its order matches the query's access pattern. A report filtering by status and date may benefit from a composite index such as (status, created_at), while a customer-history lookup may need (customer_id, order_date). The best order depends on predicates, selectivity, sorting, and workload.
Do not create an index for every date expression. Each index consumes storage and adds write cost. Measure representative queries with realistic data, inspect EXPLAIN, and retain indexes that support important workloads. Partitioning by date can help maintenance and very large scans, but it does not replace useful predicates or indexes.
Database Portability
The boundary concepts are portable even though syntax differs. MySQL uses INTERVAL 7 DAY; PostgreSQL commonly uses INTERVAL '7 days'; SQL Server uses DATEADD(day, -7, ...); Oracle supports interval expressions and date arithmetic. Date truncation, formatting, week numbering, and time-zone functions also differ.
Keep the business definition independent of vendor syntax: establish the start, establish the exclusive end, compare the temporal column directly, and convert zones deliberately. During migration, test leap dates, precision, NULL behavior, session settings, and the exact return type of every function rather than translating names mechanically.
Concurrency and Scheduled Jobs
A cleanup or reminder query can select the correct rows and still process them twice when multiple workers run concurrently. Use a claim status, transactional locking pattern, idempotency key, or unique delivery record so selection and ownership form one reliable workflow.
Scheduled jobs should also use a durable checkpoint. Instead of querying "since the last hour" after every restart, store the last successful exclusive boundary and process [previous_boundary, current_boundary). Adjacent runs then cover the timeline without gaps or overlaps, and delayed execution does not silently lose records.
Practical Checklist
- Identify whether the requirement uses a date, instant, duration, period, or interval.
- Capture one reference clock value for a consistent report or batch.
- Define the relevant business time zone.
- Use proper DATE, DATETIME, or TIMESTAMP columns.
- Prefer inclusive-start, exclusive-end ranges.
- Compute boundaries once and compare the stored column directly.
- State whether expiration and end boundaries are inclusive.
- Handle NULL according to documented business meaning.
- Separate filtering from formatting and grouping.
- Use stable tie-breakers when selecting latest or oldest rows.
- Validate indexes and plans with realistic data volumes.
- Test month, year, leap-day, precision, and daylight-saving boundaries.
Interview Questions and Answers
How do you find today's timestamped records? Use a half-open range from the current date through the next date: column >= CURRENT_DATE AND column < CURRENT_DATE + INTERVAL 1 DAY.
How do you find records from the last seven days? First clarify rolling versus calendar meaning. A rolling window commonly uses NOW() - INTERVAL 7 DAY.
How do you find expired records? Compare the expiration date or instant with the appropriate current date or current timestamp, then apply relevant status and grace-period rules.
Why avoid DATE() or YEAR() on an indexed column in WHERE? Transforming every stored value can prevent a simple index range. Compute start and end constants and compare the column directly.
What is a sargable date query? It exposes a searchable range on the indexed temporal column, allowing the optimizer to use an index efficiently when appropriate.
How do you detect overlapping bookings? Existing start must be before requested end, and existing end must be after requested start.
Why use an exclusive end? It handles arbitrary timestamp precision and lets adjacent periods meet without overlap or gaps.
How do time zones affect today's records? Today must be defined in a zone. Convert that local day's boundaries to the storage zone before filtering stored instants.
Essential Mental Model
| Problem | SQL approach |
|---|---|
| Point in time | Compare exact instants under one zone policy |
| Past rolling window | column >= current_time - interval |
| Future window | Current instant through an exclusive future boundary |
| Calendar period | Start boundary through next period boundary |
| Duration | Choose calendar or elapsed units deliberately |
| Missing event | Use IS NULL under documented semantics |
| Ongoing duration | Use COALESCE(end, current_reference) |
| Global local-day report | Convert local boundaries to the storage zone |
Key Takeaway
Real-world date SQL is primarily boundary design. Correct queries distinguish calendar dates from rolling durations, exact instants from local times, completed events from NULL-ended activity, and filtering logic from display formatting.
RELIABLE DATE QUERY = CLEAR BUSINESS MEANING
+ CORRECT TEMPORAL TYPE
+ ONE REFERENCE CLOCK
+ EXPLICIT TIME ZONE
+ INCLUSIVE START
+ EXCLUSIVE END
+ DIRECT INDEXED COMPARISON
+ NULL POLICY
+ BOUNDARY TESTS
Use CURRENT_DATE for calendar-date logic, NOW() when the current instant matters, INTERVAL expressions for relative periods, and date-difference functions only when their units match the requirement. Prefer direct ranges for filtering, apply functions for intentional calculation or presentation, and test every edge where a business period begins or ends.