Recurring schedules and time zones in PostgreSQL: patterns

Why time zones and recurring events go wrong

Most calendar bugs aren’t math bugs. They’re meaning bugs. You store one thing (an instant in time), but users expect another (a local clock time in a specific place). That gap is why recurring schedules and time zones can look fine in tests, then break as soon as real users show up.

Daylight Saving Time (DST) is the classic trigger. A shift that is “every Sunday at 09:00” is not the same as “every 7 days from a starting timestamp.” When the offset changes, those two ideas drift by an hour and your calendar quietly becomes wrong.

Travel and mixed time zones add another layer. A booking might be tied to a physical place (a salon chair in Chicago), while the person viewing it is in London. If you treat a place-based schedule as person-based, you’ll show the wrong local time to at least one side.

Common failure modes:

• You generate recurrences by adding an interval to a stored timestamp, then DST changes.
• You store “local times” without the zone rules, so you can’t rebuild the intended instants later.
• You test only dates that never cross a DST boundary.
• You mix “event time zone,” “user time zone,” and “server time zone” in one query.

Before you pick a schema, decide what “correct” means for your product.

For a booking, “correct” usually means: the appointment happens at the intended wall clock time in the venue’s time zone, and everyone viewing it gets a correct conversion.

For a shift, “correct” often means: the shift starts at a fixed local time for the store, even if an employee is traveling.

That one decision (schedule tied to a place vs. a person) drives everything else: what you store, how you generate recurrences, and how you query a calendar view without one-hour surprises.

Choose the right mental model: instant vs. local time

Many bugs come from mixing two different ideas of time:

• An instant: an absolute moment that happens once.
• A local time rule: a wall clock time like “every Monday at 9:00 AM in Paris.”

An instant is the same everywhere. “2026-03-10 14:00 UTC” is an instant. Video calls, flight departures, and “send this notification at exactly this moment” are usually instants.

Local time is what people read on a clock in a place. “9:00 AM in Europe/Paris every weekday” is local time. Store hours, recurring classes, and staff shifts are usually anchored to a location’s time zone. The time zone is part of the meaning, not a display preference.

A simple rule of thumb:

• Store start/end as instants when the event must happen at one real moment worldwide.
• Store local date and local time plus a zone ID when the event is meant to follow the clock in one place.
• If users travel, show times in the viewer’s zone, but keep the schedule anchored to its zone.
• Don’t guess a zone from offsets like "+02:00". Offsets don’t include DST rules.

Example: a hospital shift is “Mon-Fri 09:00-17:00 America/New_York.” On the DST change week, the shift is still 9 to 5 locally, even though the UTC instants move by an hour.

PostgreSQL types that matter (and what to avoid)

Most calendar bugs start with one wrong column type. The key is separating a real moment from a wall-clock expectation.

Use timestamptz for real instants: bookings, clock-ins, notifications, and anything you compare across users or regions. PostgreSQL stores it as an absolute instant and converts it for display, so ordering and overlap checks behave as expected.

Use timestamp without time zone for local wall-clock values that aren’t instants by themselves, like “every Monday at 09:00” or “store opens at 10:00.” Pair it with a time zone identifier, then convert to a real instant only when generating occurrences.

For recurring patterns, the basic types help:

• date for day-only exceptions (holidays)
• time for a daily start time
• interval for durations (like a 6-hour shift)

Store the time zone as an IANA name (for example, America/New_York) in a text column (or a small lookup table). Offsets like -0500 aren’t enough because they don’t carry daylight saving rules.

A practical set for many apps:

• timestamptz for start/end instants of booked appointments
• date for exception days
• time for recurring local start time
• interval for duration
• text for the IANA time zone ID

Data model options for booking and shift apps

The best schema depends on how often schedules change and how far ahead people browse. You’re usually choosing between writing lots of rows up front or generating them when someone opens a calendar.

Option A: store every occurrence

Insert one row per shift or booking (already expanded). It’s easy to query and easy to reason about. The tradeoff is heavy writes and lots of updates when a rule changes.

This works well when events are mostly one-off, or when you only create occurrences a short time ahead (for example, the next 30 days).

Option B: store a rule and expand at read time

Store a schedule rule (like “weekly on Mon and Wed at 09:00 in America/New_York”) and generate occurrences for the requested range on demand.

It’s flexible and storage-light, but queries get more complex. Month views can also get slower unless you cache results.

Option C: rule plus cached occurrences (hybrid)

Keep the rule as the source of truth, and also store generated occurrences for a rolling window (for example, 60-90 days). When the rule changes, you regenerate the cache.

This is a strong default for shift apps: month views stay fast, but you still have one place to edit the pattern.

A practical table set:

• schedule: owner/resource, time zone, local start time, duration, recurrence rule
• occurrence: expanded instances with start_at timestamptz, end_at timestamptz, plus status
• exception: “skip this date” or “this date is different” markers
• override: per-occurrence edits like changed start time, swapped staff member, canceled flag
• (optional) schedule_cache_state: last generated range so you know what to fill next

For calendar range queries, index for “show me everything in this window”:

• On occurrence: btree (resource_id, start_at) and often btree (resource_id, end_at)
• If you query “overlaps range” often: a generated tstzrange(start_at, end_at) plus a gist index

Representing recurrence rules without making them fragile

Recurring schedules break when the rule is too clever, too flexible, or stored as an unqueryable blob. A good rule format is one your app can validate and your team can explain quickly.

Two common approaches:

• Simple custom fields for the patterns you actually support (weekly shifts, monthly billing dates).
• iCalendar-like rules (RRULE-style) when you must import/export calendars or support many combinations.

A practical middle ground: allow a limited set of options, store them in columns, and treat any RRULE string as interchange only.

For example, a weekly shift rule can be expressed with fields like:

• freq (daily/weekly/monthly) and interval (every N)
• byweekday (an array of 0-6 or a bitmask)
• optional bymonthday (1-31) for monthly rules
• starts_at_local (the local date+time the user picked) and tzid
• optional until_date or count (avoid supporting both unless you really need it)

For boundaries, prefer storing duration (for example, 8 hours) instead of storing an end timestamp for every occurrence. Duration stays stable when clocks shift. You can still compute an end time per occurrence as: occurrence start + duration.

When expanding a rule, keep it safe and bounded:

• Expand only within window_start and window_end.
• Add a small buffer (for example, 1 day) for overnight events.
• Stop after a max number of instances (like 500).
• Filter candidates first (by tzid, freq, and start date) before generating.

Step by step: build a DST-safe recurring schedule

A reliable pattern is: treat each occurrence as a local calendar idea first (date + local time + location time zone), then convert to an instant only when you need to sort, check conflicts, or display it.

1) Store local intent, not UTC guesses

Save the schedule’s location time zone (IANA name like America/New_York) plus a local start time (for example 09:00). That local time is what the business means, even when DST shifts.

Also store a duration and clear boundaries for the rule: a start date, and either an end date or a repeat count. Boundaries prevent “infinite expansion” bugs.

2) Model exceptions and overrides separately

Use two small tables: one for skipped dates, one for changed occurrences. Key them by schedule_id + local_date so you can match the original recurrence cleanly.

A practical shape looks like this:

-- core schedule
-- tz is the location time zone
-- start_time is local wall-clock time
schedule(id, tz text, start_date date, end_date date, start_time time, duration_mins int, by_dow int[])

schedule_skip(schedule_id, local_date date)

schedule_override(schedule_id, local_date date, new_start_time time, new_duration_mins int)

3) Expand only inside the requested window

Generate candidate local dates for the range you’re rendering (week, month). Filter by day-of-week, then apply skips and overrides.

WITH days AS (
  SELECT d::date AS local_date
  FROM generate_series($1::date, $2::date, interval '1 day') d
), base AS (
  SELECT s.id, s.tz, days.local_date,
         make_timestamp(extract(year from days.local_date)::int,
                        extract(month from days.local_date)::int,
                        extract(day from days.local_date)::int,
                        extract(hour from s.start_time)::int,
                        extract(minute from s.start_time)::int, 0) AS local_start
  FROM schedule s
  JOIN days ON days.local_date BETWEEN s.start_date AND s.end_date
  WHERE extract(dow from days.local_date)::int = ANY (s.by_dow)
)
SELECT b.id,
       (b.local_start AT TIME ZONE b.tz) AS start_utc
FROM base b
LEFT JOIN schedule_skip sk
  ON sk.schedule_id = b.id AND sk.local_date = b.local_date
WHERE sk.schedule_id IS NULL;

4) Convert for the viewer at the very end

Keep start_utc as timestamptz for sorting, conflict checks, and bookings. Only when you display, convert to the viewer’s time zone. This avoids DST surprises and keeps calendar views consistent.

Query patterns to generate a correct calendar view

A calendar screen is usually a range query: “show me everything between from_ts and to_ts.” A safe pattern is:

1. Expand only candidates in that window.
2. Apply exceptions/overrides.
3. Output final rows with start_at and end_at as timestamptz.

Daily or weekly expansion with generate_series

For simple weekly rules (like “every Mon-Fri at 09:00 local”), generate local dates in the schedule’s time zone, then turn each local date + local time into an instant.

-- Inputs: :from_ts, :to_ts are timestamptz
-- rule.tz is an IANA zone like 'America/New_York'
WITH bounds AS (
  SELECT
    (:from_ts AT TIME ZONE rule.tz)::date AS from_local_date,
    (:to_ts   AT TIME ZONE rule.tz)::date AS to_local_date
  FROM rule
  WHERE rule.id = :rule_id
), days AS (
  SELECT d::date AS local_date
  FROM bounds, generate_series(from_local_date, to_local_date, interval '1 day') AS g(d)
)
SELECT
  (local_date + rule.start_local_time) AT TIME ZONE rule.tz AS start_at,
  (local_date + rule.end_local_time)   AT TIME ZONE rule.tz AS end_at
FROM rule
JOIN days ON true
WHERE EXTRACT(ISODOW FROM local_date) = ANY(rule.by_isodow);

This works well because the conversion to timestamptz happens per occurrence, so DST shifts are applied on the correct day.

More complex rules with a recursive CTE

When rules depend on “nth weekday,” gaps, or custom intervals, a recursive CTE can generate the next occurrence repeatedly until it passes to_ts. Keep recursion anchored to the window so it can’t run forever.

After you have candidate rows, apply overrides and cancellations by joining exception tables on (rule_id, start_at) or on a local key like (rule_id, local_date). If there’s a cancel record, drop the row. If there’s an override, replace start_at/end_at with the override values.

Performance patterns that matter most:

• Constrain the range early: filter rules first, then expand only within [from_ts, to_ts).
• Index exception/override tables on (rule_id, start_at) or (rule_id, local_date).
• Avoid expanding years of data for a month view.
• Cache expanded occurrences only if you can invalidate them cleanly when rules change.

Handling exceptions and overrides cleanly

Recurring schedules are only useful if you can safely break them. In booking and shift apps, the “normal” week is the base rule, and everything else is an exception: holidays, cancellations, moved appointments, or staff swaps. If exceptions are bolted on later, calendar views drift and duplicates appear.

Keep three concepts separate:

• A base schedule (the recurring rule and its time zone)
• Skips (dates or instances that must not happen)
• Overrides (an occurrence that exists, but with changed details)

Use a fixed precedence order

Pick one order and keep it consistent. A common choice:

1. Generate candidates from the base recurrence.
2. Apply overrides (replace the generated one).
3. Apply skips (hide it).

Make sure the rule is easy to explain to users in one sentence.

Avoid duplicates when an override replaces an instance

Duplicates usually happen when a query returns both the generated occurrence and the override row. Prevent it with a stable key:

• Give each generated instance a stable key, like (schedule_id, local_date, start_time, tzid).
• Store that key on the override row as the “original occurrence key.”
• Add a unique constraint so only one override exists per base occurrence.

Then, in queries, exclude generated occurrences that have a matching override and union in the override rows.

Keep auditability without friction

Exceptions are where disputes happen (“Who changed my shift?”). Add basic audit fields on skips and overrides: created_by, created_at, updated_by, updated_at, and an optional reason.

Common mistakes that cause off-by-one-hour bugs

Most one-hour bugs come from mixing up two meanings of time: an instant (a point on the UTC timeline) and a local clock reading (like 09:00 every Monday in New York).

A classic mistake is storing a local wall-clock rule as timestamptz. If you save “Mondays at 09:00 America/New_York” as a single timestamptz, you’ve already picked a specific date (and DST state). Later, when you generate future Mondays, the original intent (“always 09:00 local”) is gone.

Another frequent cause is relying on fixed UTC offsets like -05:00 instead of an IANA zone name. Offsets don’t include DST rules. Store the zone ID (for example, America/New_York) and let PostgreSQL apply the correct rules for each date.

Be careful about when you convert. If you convert to UTC too early while generating a recurrence, you can freeze a DST offset and apply it to every occurrence. A safer pattern is: generate occurrences in local terms (date + local time + zone), then convert each occurrence to an instant.

Mistakes that show up repeatedly:

• Using timestamptz to store a recurring local time-of-day (you needed time + tzid + a rule).
• Storing only an offset, not the IANA zone.
• Converting during recurrence generation instead of at the end.
• Expanding “forever” recurrences without a hard time window.
• Not testing the DST start week and DST end week.

A simple test that catches most issues: pick a zone with DST, create a weekly 09:00 shift, and render a two-month calendar that crosses a DST change. Verify every instance shows as 09:00 local time, even though the underlying UTC instants differ.

Quick checklist before you ship

Before release, check the basics:

• Every schedule is tied to a place (or business unit) with a named time zone, stored on the schedule itself.
• You store IANA zone IDs (like America/New_York), not raw offsets.
• Recurrence expansion generates occurrences only inside the requested range.
• Exceptions and overrides have a single, documented precedence order.
• You test DST change weeks and a viewer in a different time zone than the schedule.

Do one realistic dry run: a store in Europe/Berlin has a weekly shift at 09:00 local time. A manager views it from America/Los_Angeles. Confirm the shift stays 09:00 Berlin time every week, even when each region crosses DST on different dates.

Example: weekly staff shifts with a holiday and DST change

A small clinic runs one recurring shift: every Monday, 09:00 to 17:00 in the clinic’s local time zone (America/New_York). The clinic closes for a holiday on one specific Monday. A staff member is traveling in Europe for two weeks, but the clinic schedule must stay tied to the clinic’s wall clock time, not the employee’s current location.

To make this behave correctly:

• Store a recurrence rule anchored to local dates (weekday = Monday, local times = 09:00 to 17:00).
• Store the schedule time zone (America/New_York).
• Store an effective start date so the rule has a clear anchor.
• Store an exception to cancel the holiday Monday (and overrides for one-off changes).

Now render a two-week calendar range that includes a DST change in New York. The query generates Mondays in that local date range, attaches the clinic’s local times, then converts each occurrence into an absolute instant (timestamptz). Because conversion happens per occurrence, DST is handled on the right day.

Different viewers see different local clock times for the same instant:

• A manager in Los Angeles sees it earlier on the clock.
• A traveling staff member in Berlin sees it later on the clock.

The clinic still gets what it wanted: 09:00 to 17:00 New York time, every Monday that isn’t canceled.

Next steps: implement, test, and keep it maintainable

Lock down your approach to time early: will you store rule-only, occurrence-only, or a hybrid? For many booking and shift products, a hybrid works well: keep the rule as the source of truth, store a rolling cache if needed, and store exceptions and overrides as concrete rows.

Write down your “time contract” in one place: what counts as an instant, what counts as local wall time, and which columns store each. This prevents drift where one endpoint returns local time while another returns UTC.

Keep recurrence generation as one module, not scattered SQL fragments. If you ever change how you interpret “9:00 AM local time,” you want one place to update.

If you’re building a scheduling tool without hand-coding everything, AppMaster (appmaster.io) is a practical fit for this kind of work: you can model the database in its Data Designer, build recurrence and exception logic in business processes, and still end up with real generated backend and app code.