CODEWITHSUNDEEP
elixirecto
user133688
user133688

Reputation: 7064

Why is a timezone aware field timezone naive in postgres

I'm adding a utc timezone aware column to my scheme. However in my database the column typed as timestamp(0) with out zone.

What did I do wrong? My scheme looks like this:

defmodule Transaction do
    use Ecto.Schema
    import Ecto.Changeset

    schema "transaction" do
      field :platform_transaction_at, :utc_datetime
    end
end

My migration file looks like

defmodule Transaction.Migrations.AddPlatformTransactionDatetime do
  use Ecto.Migration

  def change do
    alter table(:transaction) do
      add :platform_transaction_at, :utc_datetime
    end
  end
end

Upvotes: 1

Views: 1992

Answers (1)

vaer-k
vaer-k

Reputation: 11743

PostgreSQL does not have a data type for storing a timestamp with timezone. See this elixir forum thread for more discussion. You might object that there does indeed exist a type called "timestamp with timezone", but an answer in that thread further explains:

There is the confusingly named timestamp with timezone but all it does is convert your input timestamp to UTC and convert it back to whatever your DB connection’s timezone is when reading (so for most cases it’s useless). It does not even store the offset/timezone. So you need to do that yourself somehow.

Both :utc_datetime and :naive_datetime in Ecto migrations create the same type in Postgres: timestamp without time zone http://www.creativedeletion.com/2019/06/17/utc-timestamps-in-ecto.html

The good news is that you are already storing your timestamps correctly by specifying :utc_datetime in your schema and migration, because even though it becomes a timestamp without timezone, "all timezone-aware dates and times are stored internally in UTC." https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES

Upvotes: 1

Related Questions