Time and Timestamps: Architecture and Conventions

Valid time records when the fact was true in the real world.

• valid_from: the instant from which this version of the record is authoritative (set by the INSERT trigger to current_timestamp; the application never writes it explicitly).
• valid_to: the instant from which this version is no longer current (set to the "infinity" sentinel 9999-12-31 23:59:59 for the live record; closed to current_timestamp when the record is updated or deleted).

Together they form an open interval [valid_from, valid_to). A query for "current" data filters valid_to = MAX_TIMESTAMP. A query for the state at a historical instant t filters valid_from < t AND valid_to > t=.

The application never writes valid_from or valid_to directly. The database trigger manages both columns. Entity structs therefore declare them as read-only from the application's perspective: valid_from and valid_to are populated on SELECT and ignored on INSERT/UPDATE.

recorded_at is the domain object field that surfaces valid_from to the application layer. The mapper reads it from valid_from on SELECT and never writes it back (the trigger owns it).

Every persisted entity also carries:

The stamp() helper in handler_helpers.hpp fills modified_by, performed_by, and change_reason_code on every write.

All timestamp columns are declared TIMESTAMP WITH TIME ZONE NOT NULL (or nullable where the DB schema allows NULL). PostgreSQL stores timestamps internally as UTC microseconds since 2000-01-01; the timezone is only relevant for display and input parsing. With the session timezone forced to UTC, the text representation returned to the C++ client is always "YYYY-MM-DD HH:MM:SS.ffffff+00".

// ores.database/repository/db_types.hpp
using db_timestamp = sqlgen::Timestamp<"%Y-%m-%d %H:%M:%S">;
// which is rfl::Timestamp<"%Y-%m-%d %H:%M:%S">

db_timestamp is a serialisation shim — its sole job is to let sqlgen (via rfl reflection) read and write PostgreSQL timestamp strings. Internally it wraps a std::tm (a C struct of integer fields) and uses strptime / strftime with the format "%Y-%m-%d %H:%M:%S".

Important properties and limitations:

• strptime parses "2026-06-03 10:57:32.ffffff+00" up to the seconds, stopping at the fractional-second dot. The parse succeeds and the +00 suffix is ignored. The resulting std::tm holds the correct wall-clock fields.
• db_timestamp has no concept of UTC or timezone. Whether the std::tm represents UTC or local time depends entirely on which conversion function is subsequently called.
• A default-constructed db_timestamp (std::tm{}) represents a near-epoch date (1900-01-01) — not the application epoch 1970. Calling timegm on a zero-std::tm returns a large negative time_t. The old timestamp_to_timepoint(const db_timestamp&) implementation used ts.tm() → timegm directly and silently produced epoch (or negative) values on a failed parse. This is the root cause of the "56 years ago" history display bug.
• Entity structs must declare valid_from and valid_to as plain db_timestamp (not std::optional<db_timestamp>) because the DB columns are NOT NULL. Declaring them optional meant sqlgen could silently leave them unpopulated, masking parse failures.

The canonical time type in ORE Studio. All domain structs use time_point (or std::optional<time_point> for nullable instants).

• UTC by definition (C++20 guarantees system_clock epoch is Unix epoch in UTC).
• Supports arithmetic and comparison.
• Strongly typed — no accidental local/UTC confusion.
• Converted from db_timestamp exactly once at the mapper boundary.

// The single standard conversion function.
// Parses a PostgreSQL timestamp string into a UTC time_point.
// Throws std::invalid_argument on any parse failure (empty string,
// missing UTC designator, invalid date).
inline std::chrono::system_clock::time_point
timestamp_to_timepoint(std::string_view timestamp_str);

// Overload for db_timestamp entity fields.
// Formats tm_ back to "YYYY-MM-DD HH:MM:SS" via strftime, appends
// "+00" to assert UTC intent, then delegates to from_iso8601_utc.
// Throws on a zero-initialised or otherwise invalid db_timestamp.
inline std::chrono::system_clock::time_point
timestamp_to_timepoint(const db_timestamp& ts);

Both overloads ultimately call platform::time::datetime::from_iso8601_utc. They throw rather than returning epoch silently.

// ores.platform/time/datetime.hpp
// Formats a time_point as "YYYY-MM-DD HH:MM:SS" (no timezone suffix).
// PostgreSQL accepts this for TIMESTAMPTZ when the session is UTC.
std::string datetime::to_db_string(
    const std::chrono::system_clock::time_point& tp);

This is the only function used to write timestamps to entity fields. The old timepoint_to_timestamp(tp, lg) helper (which returned an empty db_timestamp on failure — silent epoch) has been removed.

For over-the-wire serialisation, a custom rfl parser is registered in ores.utility/rfl/time_point_parser.hpp:

• Write: time_point → to_iso8601_utc → "2026-06-03 10:57:32Z"
• Read: ISO 8601 string with Z, +00, or +00:00 suffix → from_iso8601_utc → time_point. Throws on missing UTC designator.

This parser is invoked automatically when rfl serialises or deserialises any struct containing a time_point field (NATS request/response types, domain JSON I/O).

-- ores.sql/create/utility/utility_functions_create.sql
CREATE OR REPLACE FUNCTION ores_utility_infinity_timestamp_fn()
RETURNS timestamptz AS $$
    SELECT '9999-12-31 23:59:59'::timestamptz;
$$ LANGUAGE sql IMMUTABLE;

Every SQL file that references the sentinel — triggers, stored procedures, populate scripts, WHERE clauses in raw SQL strings — must call ores_utility_infinity_timestamp_fn(). Grep to audit:

# Any remaining literal usage in SQL is a bug:
grep -rn "'9999-12-31" projects/ores.sql --include="*.sql" | grep -v build

// ores.database/repository/helpers.hpp
inline constexpr const char* MAX_TIMESTAMP = "9999-12-31 23:59:59";

Every C++ repository file that must compare against the sentinel (sqlgen WHERE clauses, make_timestamp calls) must use MAX_TIMESTAMP. The default values in entity structs also use this string:

db_timestamp valid_from = "9999-12-31 23:59:59";  // matches MAX_TIMESTAMP
db_timestamp valid_to   = "9999-12-31 23:59:59";  // matches MAX_TIMESTAMP

These two constants are the only authorised representations of the sentinel. They both resolve to the same value; the split exists because PostgreSQL and C++ each have their own canonicalisation mechanism.

The commit 1ad3f36a1 replaced db_timestamp entity fields with std::string, removed the db_timestamp overload of timestamp_to_timepoint, and updated WHERE clauses to compare strings directly. This was reverted as incorrect (see below).

The actual fixes landed:

• 0446e9536: timestamp_to_timepoint(const db_timestamp&) now routes through from_iso8601_utc and throws on failure (fixes the epoch bug).
• 529baf7d7: currency_entity valid_from=/=valid_to changed from optional<db_timestamp> to db_timestamp (fixes the optional bug for currency).

The target state for the unify-entity-timestamps story is:

1. All entity NOT-NULL timestamp fields are db_timestamp (not optional<db_timestamp>). The optional was never correct for NOT NULL columns; removing it means sqlgen always populates the field and a missed read surfaces as an error rather than epoch.
2. All entity nullable timestamp fields are std::optional<db_timestamp>. These map to genuinely nullable DB columns.
3. timepoint_to_timestamp is deleted. Every mapper write site uses datetime::to_db_string(tp) instead. timepoint_to_timestamp returned an empty db_timestamp on failure (silent epoch on write).
4. (Stretch goal) Entity timestamp fields migrate from db_timestamp to std::chrono::system_clock::time_point directly. This requires confirming that sqlgen correctly handles time_point via the registered rfl custom parser. If it does, mappers become a simple assignment (r.recorded_at = v.valid_from) with no explicit conversion. This eliminates db_timestamp from the entity layer entirely and makes the type chain: TIMESTAMPTZ → time_point → time_point with no intermediate.