SQL dates and timestamps: filtering and working with time correctly

Dates and timestamps are different

Real-world data almost always contains time. Orders have creation times, payments have completion times, users have signup dates, events have timestamps. Consider the following orders table:

The ordered_at and shipped_at columns contain both a calendar date and a time of day; these are timestamps. A date value contains only the calendar date (2026-07-31), while a timestamp contains both (2026-07-31 18:30:00). This difference becomes especially important when filtering: if a column stores timestamps, asking for “orders on July 31” is not the same as checking for one exact timestamp. PostgreSQL provides separate date, timestamp, timestamp with time zone, and interval types.

Filtering a DATE column, then a timestamp column

If a column genuinely stores only a date, filtering one day is straightforward, because the value being compared represents an entire calendar date:

SELECT order_id, customer, order_date
FROM orders
WHERE order_date = DATE '2026-07-31';

But our actual ordered_at column contains timestamps. For order 1005, ordered_at = 2026-07-31 18:30:00, which is not equal to 2026-07-31 00:00:00. So timestamp filtering requires us to think in ranges.

Filtering one day from a timestamp column

“Return every order placed on July 31, 2026.” The day begins at 2026-07-31 00:00:00 and the next day begins at 2026-08-01 00:00:00. A reliable filter is:

SELECT order_id, customer, ordered_at
FROM orders
WHERE ordered_at >= TIMESTAMP '2026-07-31 00:00:00'
  AND ordered_at <  TIMESTAMP '2026-08-01 00:00:00'
ORDER BY ordered_at;

The start of July 31 is included and the start of August 1 is excluded. This form is called a half-open range (start <= value < next_start). It is especially useful for timestamps because you do not need to guess the final possible time inside the period.

The date range that loses most of the final day

Suppose the requirement is return every order placed during July 2026. Someone writes the query below. At first glance it appears to mean July 1 through July 31. But BETWEEN includes both boundary values; it is equivalent to >= lower_bound AND <= upper_bound. The upper boundary here, 2026-07-31 00:00:00, is the beginning of July 31.

SELECT order_id, customer, ordered_at
FROM orders
WHERE ordered_at BETWEEN
          TIMESTAMP '2026-07-01 00:00:00'
      AND TIMESTAMP '2026-07-31 00:00:00'
ORDER BY ordered_at;

The fix: use the start of the next period

For July 2026, use a half-open range whose upper bound is the start of August:

SELECT order_id, customer, ordered_at
FROM orders
WHERE ordered_at >= TIMESTAMP '2026-07-01 00:00:00'
  AND ordered_at <  TIMESTAMP '2026-08-01 00:00:00'
ORDER BY ordered_at;

Order 1006 is excluded because 2026-08-01 08:00:00 belongs to August. For timestamp periods, prefer timestamp >= period_start AND timestamp < next_period_start rather than trying to construct the final instant of the period.

Why not use 23:59:59?

Working with intervals

An interval represents a duration such as 7 days, 3 hours, or 30 minutes. PostgreSQL supports interval values and arithmetic between dates, timestamps, and intervals:

SELECT
    order_id,
    ordered_at,
    ordered_at + INTERVAL '7 days' AS follow_up_at
FROM orders;

For order 1001, 2026-07-01 09:15:00 plus 7 days produces 2026-07-08 09:15:00. Intervals are useful for questions such as seven days after signup, thirty days before expiration, two hours after an event, or orders created during a rolling time window.

Subtracting timestamps

We can also calculate elapsed time. To know how long it took to ship each order:

SELECT
    order_id,
    ordered_at,
    shipped_at,
    shipped_at - ordered_at AS time_to_ship
FROM orders
WHERE shipped_at IS NOT NULL
ORDER BY order_id;

In PostgreSQL, subtracting one timestamp from another produces an interval. This is useful for metrics such as time to ship, time to approve, session duration, and time between events. Rows with a NULL shipped_at are excluded here with IS NOT NULL.

Rolling time windows

“Return orders created during the previous seven days relative to the current time” uses CURRENT_TIMESTAMP and interval subtraction:

SELECT order_id, customer, ordered_at
FROM orders
WHERE ordered_at >= CURRENT_TIMESTAMP - INTERVAL '7 days';

Extracting and truncating

Sometimes we need one component from a timestamp. EXTRACT can retrieve fields such as year, month, hour, quarter, and week:

SELECT
    order_id,
    EXTRACT(YEAR  FROM ordered_at) AS order_year,
    EXTRACT(MONTH FROM ordered_at) AS order_month,
    EXTRACT(HOUR  FROM ordered_at) AS order_hour
FROM orders;

For 2026-07-15 18:45:00 we get YEAR = 2026, MONTH = 7, HOUR = 18. This is useful for questions such as which month an order occurred in, which hour receives the most activity, or which quarter contains a transaction.

Truncating timestamps with DATE_TRUNC

PostgreSQL’s DATE_TRUNC reduces a timestamp to a chosen level of precision, resetting less significant parts to the beginning of that period. 2026-07-15 18:45:00 truncated to month becomes 2026-07-01 00:00:00. This gives every timestamp from the same month the same monthly boundary. To count orders by month:

SELECT
    DATE_TRUNC('month', ordered_at) AS order_month,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', ordered_at)
ORDER BY order_month;

Unlike extracting only MONTH = 7, the truncated value includes the year, so 2025-07-01 and 2026-07-01 remain different groups. The same idea works with DATE_TRUNC('day', …), 'week', 'quarter', and 'year'. The exact available functions and syntax differ across database products.

EXTRACT and DATE_TRUNC solve different problems

EXTRACT(HOUR FROM ordered_at)     → 18            (get one component)
DATE_TRUNC('hour', ordered_at)   → 2026-07-15 18:00:00   (period boundary)

EXTRACT gets one component; DATE_TRUNC maps the timestamp to a time-period boundary.

Time zones can change the date boundary

A timestamp represents an instant, but business reporting often depends on a local calendar. One instant may be August 1 in one time zone and July 31 in another. Before calculating day, week, or month, ask which time zone defines the business period. PostgreSQL supports timestamp types with and without time-zone semantics, and AT TIME ZONE can convert values for a specific zone. Convert or interpret the timestamp in the correct business time zone before deriving calendar boundaries, and do not assume that “day” automatically means the same 24-hour boundary everywhere.

How to spot date and timestamp problems

Look carefully at date and timestamp logic when:

When the result looks wrong, inspect the boundaries first.

Answer four questions for any time-based requirement

One more prediction

Consider these orders. Requirement: return every order placed during July 2026. Which filter correctly expresses the requirement?

-- Option A
WHERE ordered_at BETWEEN
    TIMESTAMP '2026-07-01 00:00:00'
    AND TIMESTAMP '2026-07-31 00:00:00'

-- Option B
WHERE ordered_at >= TIMESTAMP '2026-07-01 00:00:00'
  AND ordered_at <  TIMESTAMP '2026-08-01 00:00:00'

-- Option C
WHERE EXTRACT(MONTH FROM ordered_at) = 7

-- Option D
WHERE ordered_at <= TIMESTAMP '2026-07-31 23:59:59'

Summary

Working with time correctly begins by understanding the value being stored. A DATE represents a calendar day; a TIMESTAMP contains both a date and a time. For timestamp filtering, prefer clear period boundaries: ordered_at >= start AND ordered_at < next_start. Intervals let you perform time arithmetic (ordered_at + INTERVAL '7 days'), and timestamp subtraction can calculate elapsed durations (shipped_at - ordered_at). Use EXTRACT when you need a component and DATE_TRUNC when you need a period boundary.

Next, we will learn how to clean, search, combine, and transform text values using SQL string operations.