SQL dates and strings: define the reporting boundary

Select a complete calendar month

This PostgreSQL example uses a timestamp without timezone in an agreed reporting timezone. The lower bound is inclusive and the next-month boundary is exclusive.

SELECT order_id, order_time, amount_usd
FROM orders
WHERE order_time >= TIMESTAMP '2026-01-01 00:00:00'
  AND order_time < TIMESTAMP '2026-02-01 00:00:00'
ORDER BY order_time, order_id;

January 31 at 23:59:59 is included; February 1 at midnight is excluded. For timezone-aware timestamps, choose the business timezone and explicit instants for the boundaries. Do not mix a rolling 30×24-hour window with 30 calendar dates.

Keep the year when grouping months

SELECT DATE_TRUNC('month', order_time) AS month_start,
  SUM(amount_usd) AS revenue_usd
FROM orders
GROUP BY DATE_TRUNC('month', order_time)
ORDER BY month_start;

Grouping only by EXTRACT(MONTH FROM order_time) combines January across every year in the input. A month-start key keeps those periods separate. Missing months are absent rows, not automatically zero revenue.

Distinguish dates from timestamps

In PostgreSQL, subtracting two dates yields an integer day count; subtracting timestamps yields an interval. A calendar-day difference and elapsed hours answer different questions, especially near daylight-saving changes.

SELECT DATE '2026-09-17' - DATE '2026-09-14' AS calendar_days;
-- Expected: 3
SELECT EXTRACT(EPOCH FROM (
  TIMESTAMP '2026-09-17 12:00:00' - TIMESTAMP '2026-09-16 00:00:00'
)) / 3600 AS elapsed_hours;
-- Expected: 36

Clean a copy and keep the original

SELECT customer_id, email AS original_email,
  LOWER(TRIM(email)) AS normalised_email
FROM customers;

A lowercase, trimmed email field can support an agreed matching rule; it is not email validation. Preserve the source value. Do not strip a country calling code from phone numbers on a global site. Formatting rules, Unicode behaviour and collation depend on your system.

Practice checks

• Test a record exactly at each boundary.
• Add January records from two years and confirm they remain separate.
• Check unknown dates and deliveries before orders instead of silently including invalid durations.
• State the database dialect: DATE_TRUNC and this date subtraction syntax are PostgreSQL examples.