Date/Time Queries for Test Data

📌 Definition:

📖 Detailed Explanation:

Temporal filtering has recurring pitfalls testers must know. First, the boundary problem: created_at BETWEEN '2026-07-01' AND '2026-07-31' often misses records late on the 31st, because a DATE literal is treated as midnight, so anything after 2026-07-31 00:00:00 is excluded — the robust pattern is created_at >= '2026-07-01' AND created_at < '2026-08-01' (half-open interval). Second, timezones: a timestamp stored in UTC can land on a different calendar day in the user's local zone, so 'orders on July 21' depends on which zone defines the day — a classic off-by-one-day bug. Third, relative ranges: 'last 30 days' should be computed from the current time (CURRENT_DATE - INTERVAL '30 days') rather than hardcoded, or tests rot. Databases differ in date syntax (INTERVAL, DATE_SUB, DATEADD), and functions like DATE_TRUNC, EXTRACT and AGE help bucket and compute durations. For testers, deliberately crafting boundary dates (exactly 30 days ago, the last second of a period) is where date bugs are caught.

🔑 Key Points:

• Prefer half-open ranges (>= start AND < next_start) over BETWEEN to avoid missing end-of-day rows
• A bare DATE literal is midnight, so BETWEEN drops most of the final day
• Timezones cause off-by-one-day bugs — know whether the day is defined in UTC or local
• Compute relative windows from now (CURRENT_DATE - INTERVAL '30 days'), don't hardcode
• Date function syntax varies (INTERVAL vs DATE_SUB vs DATEADD); test boundary dates deliberately

🌍 Real-World Example:

📎 Code Example:

-- ROBUST month filter: half-open interval captures the whole final day
SELECT id, created_at FROM orders
WHERE created_at >= '2026-07-01'
  AND created_at <  '2026-08-01';

-- FRAGILE: BETWEEN with date literals misses most of the 31st
-- WHERE created_at BETWEEN '2026-07-01' AND '2026-07-31';

-- Relative window: last 30 days, computed from now (Postgres syntax)
SELECT id FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days';

-- Bucket orders per day; find records older than a retention limit
SELECT DATE_TRUNC('day', created_at) AS day, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

SELECT id FROM sessions
WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '90 days';   -- retention check