SQL Date Filtering Returning Wrong Ranges: BETWEEN, Truncation, and Timezone Traps

You write what looks like a perfectly reasonable date filter, run the query, and the row counts are wrong. Either you're missing data from the last day of a range, pulling in records from tomorrow, or your "this month" filter is somehow including last month. The query looks correct, but the results aren't.

Date filtering in SQL is one of those areas where the syntax is simple and the edge cases are brutal. BETWEEN has a trap most people hit once. Timestamps behave differently than dates. And if your database runs in UTC while your users live in New York, you've got a third problem layered on top.

What you'll learn

• Why BETWEEN can silently exclude or include rows you didn't intend
• How timestamp truncation causes off-by-one errors on range boundaries
• How timezones shift your data into the wrong buckets
• Defensive patterns for writing date filters that actually do what you mean
• How to audit an existing broken filter and fix it without guessing

Prerequisites

This article uses examples from PostgreSQL and MySQL, but the concepts apply to any relational database. You should be comfortable reading basic SELECT queries and understand the difference between a DATE and a TIMESTAMP column at a general level.

The BETWEEN Trap

BETWEEN is inclusive on both ends. That's documented everywhere, and it's still the source of constant confusion because the behavior differs depending on your column type.

When your column is a plain DATE, this works fine:

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

You get every row where order_date falls on any day in January. No surprises. But when the column is a TIMESTAMP or DATETIME, the upper bound becomes a landmine:

-- This EXCLUDES most of January 31st
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

The string '2024-01-31' gets cast to 2024-01-31 00:00:00. So any order placed after midnight on January 31st — which is almost all of them — gets dropped. You're asking for one month of data and getting 30 days and a few seconds.

The fix: use explicit half-open intervals

Stop using BETWEEN for timestamp columns. Use a half-open interval instead:

SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01';

This pattern — greater-than-or-equal on the start, strictly less-than on the day after the end — is unambiguous regardless of the time component. It works for DATE, TIMESTAMP, and TIMESTAMPTZ columns equally well. Make it your default.

Timestamp Truncation Bugs

A related problem shows up when you're filtering by a computed value, like "show me everything from today" or "show me this week's signups."

The naive approach casts or truncates the column:

-- Slow and sometimes wrong
SELECT * FROM events
WHERE DATE(event_time) = CURRENT_DATE;

This has two problems. First, wrapping a column in a function usually prevents the database from using an index on that column, turning a fast index seek into a full table scan. Second, DATE(event_time) strips the time portion using the session's current timezone, which may not match what you think it is.

Use range comparisons on the raw column

Instead of truncating the column, compute the bounds and compare against the raw value:

SELECT * FROM events
WHERE event_time >= CURRENT_DATE
  AND event_time <  CURRENT_DATE + INTERVAL '1 day';

This is index-friendly and explicit. The database can use a B-tree index on event_time directly. The same pattern works for any time unit:

-- This month
SELECT * FROM events
WHERE event_time >= DATE_TRUNC('month', CURRENT_DATE)
  AND event_time <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

-- This week (PostgreSQL, week starts Monday by default)
SELECT * FROM events
WHERE event_time >= DATE_TRUNC('week', CURRENT_DATE)
  AND event_time <  DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week';

DATE_TRUNC is PostgreSQL syntax. MySQL users reach for DATE_FORMAT or compute the first day of the month with DATE_FORMAT(NOW(), '%Y-%m-01'). The logic is identical; only the function name differs.

The Timezone Problem

This is the trap that's hardest to debug because everything looks correct until you stare at the data long enough to notice a pattern: your "midnight cutoff" shifts by a few hours depending on the time of year, or your US East Coast customers are consistently missing from the morning bucket.

Most production databases store timestamps in UTC. That's good practice. But when your application records event_time = NOW() in UTC and your analyst queries for "events on 2024-03-15" using the database server's local time, they're filtering against the UTC value with a local frame of reference. The boundaries don't line up.

Always be explicit about timezone

In PostgreSQL, prefer TIMESTAMPTZ columns and convert at query time:

-- Convert UTC stored value to Eastern time before comparing
SELECT *
FROM events
WHERE event_time AT TIME ZONE 'America/New_York' >= '2024-03-15 00:00:00'
  AND event_time AT TIME ZONE 'America/New_York' <  '2024-03-16 00:00:00';

Or, equivalently, convert your boundary values to UTC and compare against the raw column (which is index-friendly):

SELECT *
FROM events
WHERE event_time >= '2024-03-15 00:00:00 America/New_York'::TIMESTAMPTZ
  AND event_time <  '2024-03-16 00:00:00 America/New_York'::TIMESTAMPTZ;

The second form is better for performance because it leaves the column untouched and can use an index. The cast on the literal is cheap; the function call on every row is not.

Daylight saving time edge cases

Named timezones like America/New_York are DST-aware. Using a fixed offset like -05:00 is not. If you hardcode an offset, your filter will be off by an hour for half the year. Always use a named timezone from the IANA database (America/Chicago, Europe/London, Asia/Tokyo) rather than a fixed offset.

Cross-Database Gotchas

The same logical query can behave differently across database engines. Here's a quick comparison of behaviors worth knowing:

SQLite is worth a special mention: it has no native date type. Dates are stored as text, real numbers, or integers. A BETWEEN filter on a text column does a lexicographic comparison, which works correctly only if your dates are stored in YYYY-MM-DD ISO 8601 format. Anything else and you'll get nonsense results with no error.

Dynamic Date Ranges in Reporting Queries

A lot of wrong-range bugs come from building date filters dynamically — constructing a query string in Python, passing parameters from a dashboard, or using a stored procedure that calculates start and end dates at runtime.

The failure pattern usually looks like this: someone computes end_date = today and passes it as a string. The receiving query uses BETWEEN start_date AND end_date, and because end_date is a date string that gets cast to midnight, the last day is always missing.

The fix is the same half-open interval, but you need to apply it at the parameter level too:

from datetime import date, timedelta

start_date = date(2024, 1, 1)
end_date = date(2024, 1, 31)

# Pass the exclusive upper bound
query = """
    SELECT * FROM orders
    WHERE created_at >= %(start)s
      AND created_at <  %(end)s
"""

params = {
    "start": start_date,
    "end": end_date + timedelta(days=1)  # exclusive upper bound
}

This way, the boundary logic lives in one place and the SQL never has to guess what a date string means.

Common Pitfalls Summary

• Using BETWEEN on timestamp columns — the upper bound truncates to midnight, silently dropping most of the last day.
• Wrapping the column in DATE() or DATE_TRUNC() — breaks index usage and introduces implicit timezone behavior.
• Hardcoding UTC offsets instead of named timezones — breaks twice a year when DST changes.
• Assuming the database session timezone matches your expectation — it often doesn't in cloud-managed databases where the server runs in UTC regardless of your local setting.
• Building date strings without a fixed format — locale-specific date formats (01/31/2024 vs 31/01/2024) will be misinterpreted silently in some engines.
• Forgetting that NULL timestamps are excluded from all comparisons — if a row has a NULL in the date column, it won't appear in your results, even with a very wide range. Add a separate OR created_at IS NULL clause if you need those rows.

How to Audit a Broken Filter

When a date filter is returning wrong results and you're not sure why, run these checks in order.

First, check the column type:

-- PostgreSQL
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'created_at';

If it's timestamp without time zone, you have no timezone information stored. If it's timestamp with time zone, values are stored in UTC and displayed in the session timezone.

Second, check what your session timezone is:

-- PostgreSQL
SHOW timezone;

-- MySQL
SELECT @@global.time_zone, @@session.time_zone;

Third, look at a few raw rows near your boundary to see what values are actually stored:

SELECT created_at
FROM orders
WHERE created_at >= '2024-01-31'
  AND created_at <  '2024-02-02'
ORDER BY created_at
LIMIT 20;

This will show you what's actually in the database around your boundary, and whether the stored values match your expectations about timezone and format.

Wrapping Up

Date filtering bugs are quiet. The query runs, returns rows, and shows no error. You only catch the problem when the row count is suspicious or a downstream report looks slightly off. That makes prevention more valuable than debugging.

Here are the concrete actions to take from here:

• Adopt the half-open interval pattern (>= start, < day after end) as your default for any timestamp column, and retire BETWEEN for anything more granular than a plain DATE.
• Audit your existing reporting queries for any filter that wraps a column in DATE(), DATE_TRUNC(), or CAST(... AS DATE) — those are candidates for index-killing rewrites.
• Standardize on named IANA timezones in your application and database configuration. Document which timezone your stored timestamps represent, and make that assumption explicit in your queries.
• Run a boundary test on any new date filter: insert a test row with a timestamp at exactly the start bound, one second before, one second after, and one at 23:59:59 on the last day. Confirm all four behave as you expect.
• Check your session timezone at the start of any debugging session on a cloud database. Managed services like RDS and Cloud SQL often default to UTC regardless of your local configuration.