Custom Ecto.Type for regional time zone conversion with UTC storage

Direct answer

Keep UTC in PostgreSQL and do regional conversion at the application boundary. A reusable custom Ecto.Type is appropriate only when the zone is static per schema or per tenant and known at compile time. A custom type must return a UTC DateTime struct from dump/1 to stay compatible with Postgrex and :utc_datetime / timestamptz. cast/1 should normalize ISO 8601 strings with offsets to UTC DateTime and reject or explicitly require a zone for naive strings.

Confirmed facts

• Ecto does not perform automatic regional time zone conversion. Storage is UTC, presentation is application responsibility.
• :utc_datetime maps to PostgreSQL timestamptz. Postgrex expects a DateTime with time_zone "Etc/UTC" for encoding.
• A custom Ecto.Type must implement init/1, load/1, dump/1 and cast/1. dump/1 is called before DB write, load/1 after DB read.
• Calendar.DateTime.shift_zone/3 requires tzdata to be available in the runtime.

Likely explanation and design choice

Application boundary conversion is generally preferred for per-user regional zones because Ecto.Type has no access to per-request user context during load/1 and dump/1. Performing regional conversion inside load/1 without user-specific context will produce incorrect representations for multi-region users.

A custom type can be used consistently across schemas only when the target zone is static, e.g., a tenant default stored in the same row or configured at init/1. In that case the type can shift to/from that fixed zone while still persisting UTC.

Compatibility requirements for dump/1 and load/1

Yes. For compatibility with :utc_datetime and Postgrex, dump/1 must return a DateTime struct with time_zone "Etc/UTC". If dump/1 returns a DateTime with a non-UTC zone, encoding to timestamptz becomes ambiguous and can break change tracking.

load/1 receives a UTC DateTime from the database. Converting it to a regional zone inside load/1 is only safe with a static zone. For per-user zones, load/1 should return UTC and let the application layer shift using the user’s zone.

cast/1 behavior for ISO strings

• ISO 8601 with offset, e.g., "2026-10-14T10:00:00-04:00": parse to DateTime, then convert to UTC with DateTime.shift_zone!/2. Return {:ok, utc_datetime}.
• Naive strings, e.g., "2026-10-14 10:00:00": do not assume UTC. Either return :error or require an explicit zone option. Silent assumption creates DST ambiguity.

Minimal safe steps for this case

1. Decide if zone is per-user session or static per schema/tenant. If per-user, do conversion in context, not in type.
2. If a static zone is required, define a type with init/1 receiving the zone name.
3. Implement cast/1 to accept DateTime, NaiveDateTime with explicit zone, or ISO string with offset, and normalize to UTC DateTime.
4. Implement dump/1 to ensure the value is a UTC DateTime before returning.
5. Implement load/1 to return UTC DateTime. Optionally shift to the static zone only if the type is documented as zone-aware.

Verification

• Insert a value via the type with a non-UTC offset and verify the PostgreSQL column contains the correct UTC equivalent.
• Test cast/1 with ISO strings with offsets and with naive strings to ensure consistent UTC normalization or explicit error.
• Verify load/1 returns the expected DateTime struct when queried via Ecto.Repo.

Assumption: Elixir with Ecto and Postgrex, tzdata available. Current verification required for exact Ecto version behavior of custom types with change tracking.

Missing diagnostic detail that changes recommendation: Is the required display zone known at schema compile time / per tenant row, or only at request time per user session? If per user session, avoid regional conversion inside Ecto.Type.