How to get UTC time into Postgres with Offset

Let’s say I have the below migration, schema and function. I want something_happened_at to be stored in postgres as UTC datetime object with the timezone offset. Right now I can only get the UTC time without offset into postgres datetime object. The datetime comes in as an iso 8601 string. DateTime.from_iso8601 sheds the offset when i convert it so I seemingly have no way to send to postgres with the offset other than storing the iso 8601 as a string and parsing it when I retrieve it. Any ideas on an alternate method to accomplish this?

MIgration

defmodule MyApp.Repo.Migrations.SomeMigration do
  use Ecto.Migration

  def change do
    create table(:some_table) do
      add(:user, references("users", on_delete: :nothing))
      add(:something_happened_at, :utc_datetime)

      timestamps()
    end
  end
end

Schema

defmodule MyApp.Something do

  use Ecto.Schema
  import Ecto.Changeset

  alias MyApp.{User}

  schema "some_table" do
    field(:something_happened_at, :utc_datetime_usec)

    belongs_to(:user, User, on_replace: :nilify)

    timestamps()
  end

  def changeset(something, attrs \\ %{}) do
    something
    |> cast(attrs, [
      :user_id,
      :something_happened_at
    ])
    |> validate_required([:something_happened_at])
  end
end

Function to create or update entry

def do_date_thing(date) do
  attrs = %{
    something_happened_at: DateTime.from_iso8601(date)
  }
  
  # ...send attrs to changeset
  # ...update or insert entry
end

You need to provide the time zone to DateTime functions if you want the DateTime to have one that isn’t UTC. DateTime doesn’t know if +10:00 is AEST, AEDT or something else. You can shift it using shift_zone after you’ve parsed the string but it’s still going to have to be in UTC to put it in the database.

Edit: sorry, didn’t meant to imply that timestamptz stores the offset. You can see how they did it in Calecto if you want a single column for datetime and timezone, or do a separate column for it as Ben suggested.

Neither timestamp nor timestamptz stores the offset in the DB. @CherryPoppins in such cases the offset needs to be stored in a dedicated column.

Shameless plug: TzDatetime — tz_datetime v0.1.3

2 Likes

Thanks for all the suggestions folks. I will take a look at each of them.