Introduction

Event scheduling data looks simple until you start querying it seriously. The hard part is not storing one start time and one end time, it is answering questions like “what overlaps this window?”, “what is active right now?”, and “how do recurring events fit into the model?” A design that is elegant for inserts can become slow and awkward once the query load grows.

For most applications, the cleanest design is to store actual occurrences with explicit start and end timestamps, keep them in UTC, and index the columns according to the range queries you really run. If recurring schedules exist, store the recurrence rule separately and materialize future occurrences for the window your product actually needs.

Store Occurrences, Not Just Abstract Rules

If users search for concrete event instances, your database should have concrete rows for those instances.

A simple relational design looks like this:

1CREATE TABLE events (
2    id BIGSERIAL PRIMARY KEY,
3    title TEXT NOT NULL,
4    recurrence_rule TEXT NULL
5);
6
7CREATE TABLE event_occurrences (
8    id BIGSERIAL PRIMARY KEY,
9    event_id BIGINT NOT NULL REFERENCES events(id),
10    starts_at TIMESTAMPTZ NOT NULL,
11    ends_at TIMESTAMPTZ NOT NULL,
12    CHECK (ends_at > starts_at)
13);

This model keeps event metadata in one table and queryable time ranges in another. The event_occurrences table is the one your calendar, search, and availability queries should hit.

That separation helps because recurrence logic and time-range querying are different concerns. Trying to express both through one overloaded row usually makes the schema harder to maintain.

Use UTC and Keep the Range Explicit

Store timestamps in UTC and convert for display at the edge of the system. That avoids ambiguity during daylight-saving transitions and keeps overlap logic consistent.

Two explicit columns, starts_at and ends_at, are usually enough for a portable design. A basic window query then becomes:

1SELECT id, event_id, starts_at, ends_at
2FROM event_occurrences
3WHERE starts_at < TIMESTAMPTZ '2026-03-15 18:00:00+00'
4  AND ends_at > TIMESTAMPTZ '2026-03-15 09:00:00+00'
5ORDER BY starts_at;

That condition finds every occurrence that overlaps the requested window. It is much more flexible than storing only one date column or storing a start time plus a duration string.

If your database is PostgreSQL, range types make the model even cleaner.

1CREATE TABLE event_occurrences (
2    id BIGSERIAL PRIMARY KEY,
3    event_id BIGINT NOT NULL REFERENCES events(id),
4    time_span TSTZRANGE NOT NULL
5);
6
7CREATE INDEX idx_event_occurrences_time_span
8ON event_occurrences
9USING GIST (time_span);

Then the overlap query reads naturally:

1SELECT id, event_id, time_span
2FROM event_occurrences
3WHERE time_span && TSTZRANGE(
4    TIMESTAMPTZ '2026-03-15 09:00:00+00',
5    TIMESTAMPTZ '2026-03-15 18:00:00+00',
6    '[)'
7);

Index for Your Real Query Patterns

The best schema still performs badly if the indexes do not match the workload.

For a portable two-column design, a common starting point is:

CREATE INDEX idx_event_occurrences_starts_at ON event_occurrences (starts_at);
CREATE INDEX idx_event_occurrences_ends_at ON event_occurrences (ends_at);

That helps with many “starts after” and “ends before” lookups. If your system mostly filters by tenant, calendar, or resource first, include that column in the index strategy as well.

For PostgreSQL range types, a GiST index on the range column is often the cleanest answer for overlap queries.

The main point is to avoid expressions that hide the indexed values. If you wrap columns in functions inside the WHERE clause, the planner may no longer use the index efficiently.

Handle Recurring Events Deliberately

Recurring schedules are where many schemas go wrong. Storing only a recurrence rule is compact, but it makes range queries much harder because the database has to expand the rule somehow before it can answer simple calendar questions.

A pragmatic design is:

• store the master event and recurrence rule
• materialize concrete occurrences for a future window, such as the next 90 days
• refresh that window with a background job

That gives you fast occurrence queries without expanding an infinite series on every request.

Common Pitfalls

A common mistake is storing local wall-clock strings instead of normalized timestamps. That creates timezone bugs and makes range comparisons unreliable.

Another problem is storing only one timestamp plus a duration blob. Overlap queries become harder to read and harder to index.

A third issue is generating recurring occurrences on every user request. That may feel elegant at first, but it often becomes the slowest part of the system.

Summary

• Store concrete event occurrences with explicit start and end timestamps.
• Keep timestamps in UTC and convert only at the presentation layer.
• Use overlap-friendly queries rather than equality checks on dates.
• For PostgreSQL, consider TSTZRANGE with a GiST index for cleaner range queries.
• Store recurrence rules separately and materialize occurrences for the horizon you actually query.