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
P90DandP3Mproduce 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 (optional — the walkthrough simulates every query)
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
- What is the difference between
P90DandP3M? - What does
date_duration("2025-01-31", v.x, "P1M")produce? Why? - Why is
Jan 31 + P1M - P1Mnot alwaysJan 31? - What does
sequence(v.i, 5, 5)produce? - How would you generate all Fridays in January 2025?
What You Learned
| Concept | Key Point |
|---|---|
date_duration | Tri-directional: start ↔ end ↔ duration |
| Day vs month durations | PnD is exact; PnM is calendar-relative |
| EOM preservation | Last-day-of-month stays last-day-of-month |
| Non-reversibility | +P1M then -P1M may not round-trip |
sequence | Generator for integers, decimals, dates, months, years |
weekday | ISO 8601: Mon=1, Sun=7 |
month_end_dates | Generator for month-end dates in a range |
range_min / range_max | Min/max of any comparable list |