Avoid the Midnight Timestamp Trap (Use Half-Open Ranges)

Filtering by date ranges looks simple… until it silently drops rows.

If your column is a TIMESTAMP (not DATE), using <= 'YYYY-MM-DD' can unintentionally exclude an entire day of data… except midnight.

Let’s break it down.

The Problem

Assume we have a typical table:

				
					CREATE TABLE demo_events (
  id BIGSERIAL PRIMARY KEY,
  created_timestamp TIMESTAMP NOT NULL,
  payload TEXT
);

Now suppose we want all rows in 2025 through January 28, 2026:

				
					SELECT *
FROM demo_events
WHERE created_timestamp >= '2025-01-01'
AND created_timestamp <= '2026-01-28'
ORDER BY id;

Looks fine, right?

Not quite.

What PostgreSQL / YugabyteDB Actually Does

When you write:

				
					'2026-01-28'

It is interpreted as:

				
					'2026-01-28 00:00:00'

So your predicate becomes:

				
					created_timestamp >= '2025-01-01 00:00:00'
AND created_timestamp <= '2026-01-28 00:00:00'

What Gets Excluded?

Let’s insert some sample rows:

				
					INSERT INTO demo_events (created_timestamp, payload)
VALUES
('2026-01-27 23:59:59', 'A'),
('2026-01-28 00:00:00', 'B'),
('2026-01-28 00:00:01', 'C'),
('2026-01-28 09:15:00', 'E'),
('2026-01-28 23:59:59', 'F');

Now run the original query.

You will get:

✅ 2026-01-27 23:59:59
✅ 2026-01-28 00:00:00
❌ 2026-01-28 00:00:01
❌ 2026-01-28 09:15:00
❌ 2026-01-28 23:59:59

Example:

				
					yugabyte=# SELECT *
yugabyte-# FROM demo_events
yugabyte-# ORDER BY id;
 id |  created_timestamp  | payload
----+---------------------+---------
  1 | 2026-01-27 23:59:59 | A
  2 | 2026-01-28 00:00:00 | B
  3 | 2026-01-28 00:00:01 | C
  4 | 2026-01-28 09:15:00 | E
  5 | 2026-01-28 23:59:59 | F
(5 rows)

yugabyte=# SELECT *
yugabyte-# FROM demo_events
yugabyte-# WHERE created_timestamp >= '2025-01-01'
yugabyte-# AND created_timestamp <= '2026-01-28'
yugabyte-# ORDER BY id;
 id |  created_timestamp  | payload
----+---------------------+---------
  1 | 2026-01-27 23:59:59 | A
  2 | 2026-01-28 00:00:00 | B
(2 rows)

You unintentionally excluded the entire day of January 28 except midnight.

That’s a subtle bug, and in production systems, subtle bugs are the worst kind.

Why This Matters in Distributed Systems

In distributed databases like YugabyteDB, timestamp filters often drive:

● Index scans
● Tablet pruning
● Partition elimination
● Time-based workload routing

If your upper bound is wrong, you may:

● Return incomplete results
● Misinterpret performance results
● Incorrectly size partitions
● Miss entire shards of data

Small predicate mistakes scale into big production problems.

The Correct Pattern: Half-Open Ranges

Instead of:

				
					created_timestamp <= '2026-01-28'

Use:

				
					created_timestamp < '2026-01-29'

Full query:

				
					SELECT *
FROM demo_events
WHERE created_timestamp >= '2025-01-01'
AND created_timestamp <  '2026-01-29'
ORDER BY id;

Now you correctly capture the full day of January 28.

Example:

				
					yugabyte=# SELECT *
yugabyte-# FROM demo_events
yugabyte-# WHERE created_timestamp >= '2025-01-01'
yugabyte-# AND created_timestamp <  '2026-01-29'
yugabyte-# ORDER BY id;
 id |  created_timestamp  | payload
----+---------------------+---------
  1 | 2026-01-27 23:59:59 | A
  2 | 2026-01-28 00:00:00 | B
  3 | 2026-01-28 00:00:01 | C
  4 | 2026-01-28 09:15:00 | E
  5 | 2026-01-28 23:59:59 | F
(5 rows)

Why This Is Best Practice

This pattern:

				
					column >= start_date
AND column <  next_day

✔ Works for TIMESTAMP
✔ Works for TIMESTAMPTZ
✔ Avoids microsecond precision edge cases
✔ Avoids timezone confusion
✔ Plays nicely with indexes
✔ Works perfectly with range partition pruning

It is the standard approach in high-scale PostgreSQL systems… and it applies directly to YugabyteDB.

Final Thought

This is one of those small refinements that makes your distributed SQL queries precise and predictable. In single-node databases it’s a correctness issue. In distributed systems, it becomes a correctness and performance issue.

Tiny detail. Big impact.

Have Fun!