Relix

Problem solving

As of, during, overlapping

Grain: one row per event (as-of), or one per overlapping pair (interval) · Class: Temporal alignment · Signals: as of, at the time, in effect, during, while, overlapping, conflicts with · Operators: ASOF, IJOIN

The problem

"What exchange rate was in effect at the moment of each order? And which room reservations overlap a maintenance window?"

How to recognise it

The question aligns rows in time rather than on an exact key. Two shapes, and the tell is whether each side is a point or a period:

Both need real temporal values. An inline table holds text, so convert the columns with to_timestamp first — text sorts chronologically for ISO-8601, but only a real TIMESTAMP has a distance for a tolerance to measure.

Recipe 1: the value in effect at a moment (ASOF)

Each order should carry the exchange rate that was current at or before it. An AS-OF join snaps each left row to the nearest right row in the direction the inequality names — >= is backward (at or before, the "as of" default).

Query
RatesRaw := [
| at                   | rate |
|----------------------|------|
| 2026-01-02T09:00:00Z | 0.80 |
| 2026-01-02T12:00:00Z | 0.82 |
];

OrdersRaw := [
| at                   | usd |
|----------------------|-----|
| 2026-01-02T10:30:00Z | 100 |
| 2026-01-02T13:00:00Z | 200 |
| 2026-01-02T08:00:00Z | 50  |
];

Rates  := { π to_timestamp(at) → at, rate (RatesRaw) };
Orders := { π to_timestamp(at) → at, usd  (OrdersRaw) };

query { Orders ASOF Orders.at >= Rates.at Rates };
Result
 at                    usd  at_r                  rate
 ────────────────────  ───  ────────────────────  ────
 2026-01-02T10:30:00Z  100  2026-01-02T09:00:00Z   0.8
 2026-01-02T13:00:00Z  200  2026-01-02T12:00:00Z  0.82
 2026-01-02T08:00:00Z   50  NULL                  NULL
(3 rows)

By default an order with no prior rate keeps NULLs (left-outer). The 08:00 order has no rate before it. Add INNER to drop such rows, and now the conversion pays off:

Query
query { π at, usd, rate, usd * rate → gbp (Orders ASOF INNER Orders.at >= Rates.at Rates) };
Result
 at                    usd  rate  gbp
 ────────────────────  ───  ────  ───
 2026-01-02T10:30:00Z  100   0.8   80
 2026-01-02T13:00:00Z  200  0.82  164
(2 rows)

WITHIN DURATION 'PT30M' adds a staleness bound — reject a match older than the tolerance — and <= flips the direction to the next rate at or after the order.

Recipe 2: periods that overlap (IJOIN)

Which reservations clash with a maintenance window? Each side is an interval, and "clash" is any shared time — Allen's INTERSECTS. An interval join names the endpoints of each side and applies the geometric predicate for you:

Query
MaintRaw := [
| room | start                | finish               |
|------|----------------------|----------------------|
| 101  | 2026-03-04T00:00:00Z | 2026-03-06T00:00:00Z |
];

ResRaw := [
| res | room | checkin              | checkout             |
|-----|------|----------------------|----------------------|
| R1  | 101  | 2026-03-05T14:00:00Z | 2026-03-08T11:00:00Z |
| R2  | 101  | 2026-03-06T14:00:00Z | 2026-03-09T11:00:00Z |
| R3  | 102  | 2026-03-05T14:00:00Z | 2026-03-08T11:00:00Z |
];

Maint := { π room, to_timestamp(start) → start, to_timestamp(finish) → finish (MaintRaw) };
Res   := { π res, room, to_timestamp(checkin) → checkin, to_timestamp(checkout) → checkout (ResRaw) };

query {
    σ room = room_r (
        Res IJOIN INTERSECTS (Res.checkin, Res.checkout, Maint.start, Maint.finish) Maint)
};
Result
 res  room  checkin               checkout              room_r  start                 finish
 ───  ────  ────────────────────  ────────────────────  ──────  ────────────────────  ────────────────────
 R1    101  2026-03-05T14:00:00Z  2026-03-08T11:00:00Z     101  2026-03-04T00:00:00Z  2026-03-06T00:00:00Z
(1 row)

The interval join matches on endpoints only, so the same-room condition is a σ on top — and the right side's room arrives disambiguated as room_r.

Variations

Pitfalls

Check it