Preserving Original Local Time vs UTC-Normalized Storage in Ecto Schemas

Direct answer

Adopt a hybrid pattern: store the user's exact wall-clock time in a naive_datetime_usec column, store the IANA time zone in a separate varchar column, and add a generated timestamptz column for UTC-based analytics. This reconstructs the original local time exactly while letting PostgreSQL index and sort the derived UTC value.

Confirmed facts vs likely explanation

Confirmed facts

• :utc_datetime_usec maps to PostgreSQL timestamptz, which normalizes input to UTC and discards the source offset.
• :naive_datetime_usec maps to PostgreSQL timestamp without time zone, preserving the entered wall-clock value.
• PostgreSQL can index a generated timestamptz column for efficient range and ordering queries.

Likely explanation

The application reads local_time and time_zone together to reproduce the user's original input. Analytics queries filter and sort directly on utc_time, avoiding per-row application conversion.

Implementation steps

1. Add columns to the scheduling table. Assuming Ecto 3.10+ and PostgreSQL 12+:

def change do
  alter table(:schedules) do
    add :local_time, :naive_datetime_usec
    add :time_zone, :string
  end

  execute """
    ALTER TABLE schedules
    ADD COLUMN utc_time timestamptz
    GENERATED ALWAYS AS (local_time AT TIME ZONE time_zone) STORED;
  """

  create index(:schedules, [:utc_time])
end

1. Declare the fields in the schema. Mark the generated column as read-after-write so Ecto does not include it in inserts or updates:

schema "schedules" do
  field :local_time, :naive_datetime_usec
  field :time_zone, :string
  field :utc_time, :utc_datetime_usec, read_after_writes: true

  timestamps()
end

1. Insert using local time and zone, then read utc_time back from the database for analytics queries.

Verification

• Run psql -c '\d schedules' and confirm utc_time is timestamp with time zone GENERATED ALWAYS AS ... STORED.
• Insert ~N[2024-03-10 02:30:00] with 'America/New_York' and confirm local_time round-trips unchanged while utc_time shows the normalized instant.
• Run EXPLAIN on a WHERE utc_time >= ... query to confirm the index is used.

Assumptions and open detail

This assumes PostgreSQL 12 or newer for stored generated columns. It also assumes you store IANA zone names rather than raw offsets, because offsets alone do not represent historical DST rules. Ambiguous wall-clock times during fall-back transitions still require application-level disambiguation.

One missing diagnostic detail changes this recommendation: is your production PostgreSQL version 12 or newer? If not, stored generated columns are unavailable and you should maintain the UTC column in the application or via a trigger instead.