Timezone handling

Genie stores timestamps in UTC and converts them to each user’s local zone for display and input. Every request carries an effective session time zone; the engine exposes it to your SQL so that date logic can be tenant- and user-correct without hard-coding an offset.

The rule

• Storage is UTC. DateTimeOffset is used for timezone-correct persistence.
• The UI converts. The React client formats UTC values into the user’s zone for display and converts local input back to UTC before sending — the backend never renders a localized string.
• SQL gets the zone as parameters (below), so server-side date expressions can localize too.

Server-side rule: never DateTime.Now

Engine and host code must use DateTimeOffset.UtcNow for anything that is persisted or used as a query parameter. Never DateTime.Now or DateTimeOffset.Now.

This is a hard requirement, not a style preference. DateTime.Now implicitly converts to a DateTimeOffset carrying the machine’s local offset, and on PostgreSQL the trait/audit columns are timestamp with time zone. Npgsql rejects any non-zero offset against that type — on writes and on WHERE-clause parameters:

System.ArgumentException: Cannot write DateTimeOffset with Offset=05:00:00 to PostgreSQL type
'timestamp with time zone', only offset 0 (UTC) is supported.

So on a host in any non-UTC time zone, a single DateTime.Now is a runtime exception rather than a slightly-wrong value. Do not work around it with AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true) — that switch silences the whole class of error, including cases where the offset genuinely is wrong.

Two safety nets back this up:

• Trait timestamps are normalized on save. GenieContext.SaveChanges rewrites CreatedAt, UpdatedAt and DeletedAt to UTC before they reach the provider, preserving the instant. This covers host code the engine does not own. It does not excuse local-time call sites — every other column is on you.
• A build-time guard. NoLocalNowInEngineTests scans the engine sources and fails on any new DateTime.Now / DateTime.Today / DateTimeOffset.Now, against an allowlist of the deliberate display-only sites (filenames, sequence-number {yy}/{MM} tokens, in-memory metrics). Local time is fine for those — just not for anything that reaches the database.

The same rule applies to SQL you author: prefer SYSUTCDATETIME() on SQL Server and now() on PostgreSQL over SYSDATETIME() / LOCALTIMESTAMP.

Resolving the session time zone

SessionTimeZoneService (ISessionTimeZoneService) resolves the effective IANA zone id in priority order, always validating the id before use:

1. Session cookie — an explicit override set via the API (SessionTimeZone cookie).
2. User preference — the persisted TimeZoneId on the user record.
3. X-TimeZone request header — the browser’s auto-detected zone, sent by the client.
4. UTC — the default fallback.

An invalid id at any level is skipped rather than trusted.

SQL session parameters

Two parameters are available to any view/entity/badge SQL, populated per request from the resolved zone (see Entities → session parameters for the full list):

Parameter	Value
@SessionTimeZone	the caller’s effective IANA zone (e.g. Asia/Karachi)
@SessionTimeZoneOffset	that zone’s current UTC offset, in minutes

Localize a UTC column dialect-neutrally via the offset, or — for full DST-correctness across arbitrary dates on PostgreSQL — via the named zone:

-- SQL Server
DATEADD(MINUTE, @SessionTimeZoneOffset, SYSUTCDATETIME())

-- PostgreSQL (offset)
now() + (@SessionTimeZoneOffset || ' minutes')::interval

-- PostgreSQL (DST-correct for any date)
utcColumn AT TIME ZONE @SessionTimeZone

API

Method	Route	Purpose
POST	/api/v1/genie/timezone/switch	set the session zone ({ TimeZoneId }); writes the cookie and persists it as the user’s preference
GET	/api/v1/genie/timezone/current	get the current effective IANA zone id

The switch cookie is HttpOnly + Secure + SameSite=Strict with a one-year expiry, so the override survives across sessions until changed. A Profile time-zone picker in the UI drives this endpoint.