Tutorial 1: Dates, Comparisons & Range Queries

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

What you will learn

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

• Represent dates and times as typed literals in WOQL
• Compare dates with less, greater, lte, and gte
• Filter values into half-open ranges with in_range
• Explain the difference between "as of" and "period" queries
• Know when to use triple_slice for O(log n) lookups on stored data

Prerequisites

• A running TerminusDB instance (Docker is easiest: docker run --rm -p 6363:6363 terminusdb/terminusdb-server)
• Basic familiarity with WOQL concepts (WOQL Tutorial)

Every playground below runs against your local TerminusDB. Click Settings on any playground to change the server URL or credentials. The time predicates work in anonymous mode — no database needed — because they are pure computations.

Part 1: Typed Literals — How Dates Are Represented

Every value in WOQL is a typed literal: a value string paired with a type. The literal() function creates one. Run this to see what a date looks like:

What happened: The variable v:today is now bound to a value with type xsd:date and value "2025-03-31". Every date in TerminusDB carries its type — this is how the system knows to compare it as a calendar date, not as a string.

The date and time types you will use most

Try changing the type and value in the playground above. What happens if you use xsd:gYearMonth with "2025-03"?

Part 2: Comparing Dates

WOQL provides four comparison predicates. They work on every ordered type — dates, numbers, strings.

Step 2: Strict comparison

What happened: The result shows true (1 row returned). The less predicate succeeds because January 1 is before March 31.

Try changing the dates so the first is after the second — you'll see false (0 rows) because the comparison fails.

Step 3: Boundary equality

What happened: The result shows true. The gte (greater than or equal) predicate succeeds when values are equal — that's the "or equal" part. If you used greater instead, you'd see false because the dates are not strictly greater.

Key insight: This distinction matters for "as of" queries (covered below). Use gte when you want to include the boundary date, greater when you want to exclude it.

Step 4: Compare different types

Part 3: Half-Open Range Filtering with in_range

in_range(Value, Start, End) succeeds when Start ≤ Value < End. The start is inclusive, the end is exclusive. This [start, end) convention is called a half-open range.

Step 5: Value inside the range

Step 6: Start is inclusive

Step 7: End is exclusive

Why half-open? Because adjacent ranges partition cleanly:

• Q1 = [Jan 1, Apr 1) — contains Mar 31, not Apr 1
• Q2 = [Apr 1, Jul 1) — contains Apr 1, not Jul 1
• No date falls in both. No date falls in neither. No gap between them.

Step 8: Integer ranges work too

Part 4: "As Of" vs "Period" Queries {#as-of}

This is the most important distinction in financial time processing. Mixing up these two patterns is the #1 source of off-by-one-day bugs.

Pattern 1: "As of" — cumulative state at a point in time

"What is the balance as of March 31?" means: all transactions up to and including that date.

The query pattern uses lte — the as-of date is inclusive:

// "As of March 31" — lte is inclusive
and(
  triple(v.doc, "transaction_date", v.date),
  lte(v.date, literal("2025-03-31", "xsd:date"))
)

Pattern 2: "During period" — transactions in a range

"Show me Q1 transactions" means: transactions in the half-open interval [Jan 1, Apr 1).

The query pattern uses in_range — the end date is exclusive:

// "During Q1" — in_range has exclusive end
and(
  triple(v.doc, "transaction_date", v.date),
  in_range(v.date,
    literal("2025-01-01", "xsd:date"),
    literal("2025-04-01", "xsd:date"))
)

The difference that matters

Both include March 31. But "as of" also includes everything before Q1. The period query only includes Q1. And critically, the "as of" query uses the inclusive end (Mar 31), while the period query uses the exclusive end (Apr 1).

Part 5: Working with Stored Data

The playgrounds above used anonymous mode (pure computation). For queries on stored data, you need a database. The patterns below work with any schema that has date properties.

triple_slice — O(log n) range queries

When data is stored in TerminusDB, triple_slice pushes range constraints into the storage engine. It replaces triple + in_range with a single indexed lookup:

// Instead of this (scans all values):
and(
  triple(v.doc, "transaction_date", v.date),
  in_range(v.date, start, end)
)

// Use this (indexed lookup):
triple_slice(v.doc, "transaction_date", v.date, start, end)

Deep dive: See the triple_slice guide for advanced usage.

Part 6: Timezone Handling

Dates are timezone-agnostic

Calendar dates ignore timezone offsets for comparison and arithmetic:

DateTimes normalize to UTC

DateTime values are converted to UTC before comparison. 23:00+02:00 is 21:00Z, which is before 22:00Z:

For financial systems: store timestamps in UTC. Timezone conversion is a display concern, not a data concern.

Self-Check

Before moving to the next tutorial, make sure you can answer these:

1. What is the difference between less and lte?
2. Why does in_range(10, 5, 10) fail?
3. When would you use lte instead of in_range for a date query?
4. Why does triple_slice exist when in_range already works?

What You Learned

Next

Tutorial 2: Durations, Month Arithmetic & Sequences →