Storing timestamps as native datetime in Neo4j

A timestamp stored as the string "2026-07-17T09:30:00Z" or the epoch integer 1752745800 looks harmless until the first range query runs against it. Because neither type carries temporal ordering the planner can reason about, WHERE n.created_at >= $since degrades to a full label scan with a per-row string comparison, and every duration calculation forces a parse at read time. This page shows how to store timestamps as Neo4j 5.x native temporal values — datetime, date, localdatetime — so the same predicate becomes an index-backed range seek, how to convert an existing string or epoch column in place, and how to handle timezone offsets so a naive value never silently loses its zone. It is a concrete application of graph data type selection to the property that most often ships as the wrong type.

Prerequisites

• Neo4j 5.x and the neo4j Python driver 5.x, whose neo4j.time classes serialize directly to native temporal values.
• The type-selection policy from graph data type selection: the most specific native type that faithfully represents the value, never a String widened for convenience.
• A settled node label taxonomy so the range index you create attaches to one stable label.
• Knowledge of each timestamp’s source semantics — is it an instant with a real UTC offset, or a wall-clock reading with no zone? The two map to different native types.
• A pre-conversion snapshot taken with neo4j-admin database dump, because a type rewrite over a large label is a schema migration, not an update.

Why the string never seeks

A range index over a native datetime stores values in temporal order, so a >= $since predicate lands on a NodeIndexSeekByRange that jumps straight to the boundary and reads only the matching tail. A String holding an ISO timestamp is ordered lexically, not temporally — the index can only accelerate exact-match and prefix lookups, so any inequality falls back to scanning every node of the label and coercing each value. An epoch Integer is worse in a different way: it will range-scan numerically, but it cannot answer duration.between, cannot be truncated to a day with date.truncate, and silently discards the offset the source recorded. Native types keep all three capabilities — ordering, index eligibility, and calendar-aware arithmetic — in one value.

The diagram contrasts the two execution paths for the identical predicate.

Core implementation

The whole discipline is: cast at the ingestion boundary so the value arrives native, back the property with a range index, and — for an existing column — rewrite the string in batches with datetime(). The comments mark the three temporal choices and the timezone rule.

// 1) BACK THE PROPERTY WITH A RANGE INDEX so range predicates seek, not scan.
//    A property-type constraint additionally rejects any future non-temporal write.
CREATE INDEX event_occurred_at IF NOT EXISTS
FOR (e:Event) ON (e.occurred_at);
CREATE CONSTRAINT event_occurred_at_type IF NOT EXISTS
FOR (e:Event) REQUIRE e.occurred_at IS :: ZONED DATETIME;

// 2) INGEST AS NATIVE. Choose the type by the value's real semantics:
//      datetime()      -> instant WITH a zone/offset  (an event that happened)
//      localdatetime() -> wall-clock, NO zone         (a store's 09:00 opening)
//      date()          -> calendar day                (a birth date, a due date)
//    datetime() parses an ISO-8601 string OR takes { epochSeconds: ... }, and it
//    normalises the offset so ordering and comparison are well-defined.
UNWIND $rows AS row
MERGE (e:Event {event_id: row.event_id})
SET e.occurred_at = datetime(row.occurred_at),                  // "2026-07-17T09:30:00+02:00"
    e.local_open  = localdatetime(row.local_open),              // "2026-07-17T09:00:00"
    e.event_day   = date(row.event_day);                        // "2026-07-17"

// 3) CONVERT AN EXISTING STRING COLUMN IN PLACE, in bounded batches so the
//    rewrite never opens one giant transaction. Guard on the string type so a
//    replay skips rows already converted. Epoch ints use datetime({epochSeconds:x}).
MATCH (e:Event) WHERE e.occurred_at IS :: STRING
CALL (e) {
  SET e.occurred_at = datetime(e.occurred_at)
} IN TRANSACTIONS OF 10000 ROWS;

Three details decide correctness. First, datetime() on a string with no offset produces a zoned value anchored to the database’s configured default zone — if the source string is genuinely zoneless wall-clock, that invents a false offset, so use localdatetime() instead and keep the zone out of the model entirely. Second, an epoch integer is unambiguously UTC, so datetime({epochSeconds: row.ts}) is exact and needs no zone guess. Third, the in-place conversion is guarded by IS :: STRING, which makes it idempotent: a re-run finds the already-converted rows are no longer strings and skips them, the same convergence property that governs every idempotent migration script.

From the Python side, pass a neo4j.time.DateTime (or a timezone-aware datetime.datetime) so the driver serializes a zoned value; a naive Python datetime maps to LocalDateTime and drops the offset. Normalize to UTC-aware at the boundary before the value ever reaches Bolt.

Validation & verification

Prove the property is genuinely temporal and index-backed. First, confirm no string or integer stragglers survived the conversion — a non-zero count is unconverted data that will still scan:

MATCH (e:Event)
RETURN count(CASE WHEN e.occurred_at IS :: ZONED DATETIME THEN 1 END) AS native,
       count(CASE WHEN NOT e.occurred_at IS :: ZONED DATETIME THEN 1 END) AS not_native;

Then confirm the range query actually seeks. Run PROFILE over the predicate and require a NodeIndexSeekByRange leaf; an AllNodesScan or NodeByLabelScan with a trailing Filter means the index is missing, still POPULATING, or the property is not uniformly temporal:

PROFILE
MATCH (e:Event)
WHERE e.occurred_at >= datetime($since) AND e.occurred_at < datetime($until)
RETURN e.event_id
ORDER BY e.occurred_at;

Finally, exercise the payoff that strings can never give you — duration math directly in Cypher: RETURN duration.between(e.occurred_at, datetime()).days returns a calendar-aware age with no parsing.

Edge cases & gotchas

1. Mixed types on the same property across nodes. If half the Event nodes carry occurred_at as a datetime and half as a String, the range index fragments and the planner’s histograms go stale, so plan choice flips unpredictably between seek and scan. Convert the whole label in one migration and pin the result with the property-type constraint above so a stray string write is rejected at commit — the type-drift failure catalogued in graph data type selection.

2. Assuming datetime() knows the source zone. A string like "2026-07-17 09:30:00" with no offset is ambiguous. datetime() resolves it against the DB default zone, which is rarely what the source meant:

// If the reading is true wall-clock, keep it zoneless — do not invent an offset.
RETURN localdatetime("2026-07-17T09:30:00") AS wall_clock;

3. Truncating to a day with string slicing. Reaching for substring(e.created_at, 0, 10) to group by day is a scan in disguise. Store a native value and use date.truncate('day', e.occurred_at), which stays index-friendly and zone-correct.

This task sits under graph data type selection; the same “most specific native type” rule governs money, measurements, and spatial points there.

Related

• Up: Graph Data Type Selection — the type-selection contract this timestamp rule applies.
• Schema Evolution & Versioning — running the string-to-datetime rewrite as a safe, versioned migration.
• Designing Temporal Graphs for Audit-Trail Compliance — modeling validity intervals once timestamps are native.