Tutorial 2: Durations, Month Arithmetic & Sequences

Series: Time Processing Overview | Tutorial 1: Dates | Tutorial 2 | Tutorial 3: Intervals | Tutorial 4: Creative Patterns

What you will learn

By the end of this tutorial you will be able to:

• Add and subtract durations from dates using date_duration
• Explain why P90D and P3M produce different results
• Describe the end-of-month preservation rule and why it matters for finance
• Generate sequences of dates, months, and numbers with sequence
• Extract weekdays and ISO week numbers from dates
• Find minimum and maximum values with range_min / range_max

Prerequisites

• Completed Tutorial 1: Dates
• A running TerminusDB instance

Part 1: Duration Arithmetic with date_duration

date_duration(Start, End, Duration) relates three values. It is tri-directional — give it any two and it computes the third.

Step 1: Add days to a date

What happened: v:deadline is bound to 2025-05-30. The duration P60D means exactly 60 calendar days. The P prefix is ISO 8601 for "period".

Step 2: Compute the duration between two dates

Result: P90D — that is 31 (Jan) + 28 (Feb, non-leap) + 31 (Mar) = 90 days.

Step 3: Compute the start from end minus duration

Step 4: Validate a relationship

When all three arguments are ground, date_duration acts as a validator — it succeeds only if the relationship is correct:

Now try changing P90D to P89D — it will return zero results because the relationship is wrong.

Part 2: Why P90D and P3M Are Different

This is the most important concept in this tutorial. Day durations are exact. Month durations are calendar-relative.

Step 5: Day-based duration

Step 6: Month-based duration

Both land on April 1 — but P3M covers 90 days in 2025 (non-leap) and 91 days in 2024 (leap). The month duration preserves the calendar position, not the day count.

Practical rule: Use PnD for exact day counts (regulatory deadlines: "within 60 days"). Use PnM for calendar positioning (payment schedules: "every month on the same date").

Part 3: End-of-Month Preservation

What happens when you add a month to January 31? February has no 31st day.

Step 7: The EOM rule

Result: 2025-02-28. Because January 31 is the last day of January, the result is the last day of February. This is end-of-month (EOM) preservation.

Step 8: The EOM chain

The chain never breaks. This is exactly what financial systems need for monthly payment schedules and interest accruals.

Step 9: Non-reversibility warning

Key insight: Month arithmetic is not always reversible. Use day-count durations (P90D) when round-trip accuracy matters.

Deep dive: The EOM Preservation Rules page has the complete rule set with addition and subtraction tables.

Part 4: Date Navigation

Step 10: day_after and day_before

Try changing the year to 2024 (leap year) — day_after Feb 28 will give Feb 29, not Mar 1.

Step 11: Weekday extraction

Step 12: ISO week number

Part 5: Sequence Generation

sequence(Value, Start, End) generates values in a half-open range [Start, End). When Value is unbound, it produces each value via backtracking.

Step 13: Integer sequence

Step 14: Monthly reporting calendar

Step 15: Weekly dates with a step

Step 16: Decimal sequence — no floating-point drift

Step 17: Empty range = zero results

Part 6: Month Boundaries

Financial reporting revolves around month boundaries — period starts, period ends, accrual dates.

Step 18: Last day of a month

Step 19: All month-end dates in a range

Part 7: Range Min and Max

range_min and range_max find the smallest and largest values in a list.

Step 20: Find the min and max

Self-Check

1. What is the difference between P90D and P3M?
2. What does date_duration("2025-01-31", v.x, "P1M") produce? Why?
3. Why is Jan 31 + P1M - P1M not always Jan 31?
4. What does sequence(v.i, 5, 5) produce?
5. How would you generate all Fridays in January 2025?

What You Learned

Next

Tutorial 3: Intervals & Allen's Temporal Algebra →