Q26 of 26 · SQL

How do you use date and time functions in SQL for QA validation?

SQLMidsqldate-functionstimestamptime-validationinterval

Short answer

Short answer: Date functions let you query records relative to 'now', calculate elapsed time, validate that timestamps fall within expected windows, and detect stale or future-dated data.

Detail

Getting current time:

NOW()          -- current timestamp with time zone (PostgreSQL, MySQL)
CURRENT_TIMESTAMP  -- ANSI standard
GETDATE()      -- SQL Server

Date arithmetic — records from the last N hours/days:

-- PostgreSQL / MySQL 8
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '24 hours';
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

-- SQL Server
SELECT * FROM orders WHERE created_at > DATEADD(hour, -24, GETDATE());

QA validation patterns:

  1. Assert a record was created within the last minute (after an API call):
SELECT COUNT(*) FROM audit_log
WHERE event_type = 'user.created'
  AND created_at > NOW() - INTERVAL '1 minute';
-- Expect 1
  1. Find stale/stuck records (orders pending more than 48 hours — possible bug):
SELECT order_id, created_at, status FROM orders
WHERE status = 'pending'
  AND created_at < NOW() - INTERVAL '48 hours';
  1. Find future-dated records (system clock bug or timezone issue):
SELECT * FROM orders WHERE created_at > NOW();
-- Should return 0 rows
  1. Extract parts for grouping:
SELECT DATE_TRUNC('hour', created_at) AS hour_bucket, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

// EXAMPLE

-- Detect timezone bugs: orders with future timestamps
SELECT COUNT(*) AS future_orders
FROM orders
WHERE created_at > NOW();
-- Expect 0; non-zero means a timezone offset error

// WHAT INTERVIEWERS LOOK FOR

NOW() - INTERVAL pattern for time-scoped assertions. At least one concrete QA check (stale records, future timestamps, within-N-minutes assertion).

// Related questions

How do you validate database state after an automated test runs?

SQL