Skip to content

timezones-postgres-phoenix.md

Latest commit

 

History

History
165 lines (115 loc) · 5.5 KB

timezones-postgres-phoenix.md

File metadata and controls

165 lines (115 loc) · 5.5 KB

Bottom line is to use the following:

  • MIGRATION: timestamptz aka. timestamp with timezone (the Ecto default is timestamp and :naive_datetime)

  • SCHEMA: :utc_datetime (the Ecto default is :naive_datetime

Basically none of the recommended types are the default.

ECTO MIGRATIONS: Switch to timestamptz and :utc_datetime

Note: Ecto.Migration.timestamps/1 (source) global configuration can always be overridden locally.

1. Global configuration

Using the :migration_timestamps configuration option from the Ecto.Migration docs:

# in ./config/dev.exs (for example)

config :app, App.Repo, migration_timestamps: [type: :timestamptz]

and one can use Ecto.Migration.timestamps/1 in migrations as usual:

# ./priv/repo/migrations/20190718195828_create_users.exs

create table(:users) do
  add :username, :string, null: false

  timestamps()
end

The Postgres adapter will automatically switch the Elixir representation to DateTime from NaiveDateTime.

2. Local configuration

Use Ecto.Migration.timestamps/1's :type option:

defmodule App.Repo.Migrations.CreateUsers do

  use Ecto.Migration

  def change do
    create table(:users) do
      add :username, :string, null: false

      timestamps(type: :timestamptz)
    end
  end
end

ECTO SCHEMAS: Switch to :utc_datetime

1. Global configuration

The Ecto schemas also need to be modified to use :utc_datetime, otherwise they will expect NaiveDateTime by default. Slightly modifying the example in the Ecto.Schema docs:

# Define a module to be used as base
defmodule MyApp.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema

      # In case one uses UUIDs
      @primary_key {:id, :binary_id, autogenerate: true}
      @foreign_key_type :binary_id

      # ------------------------------------
      @timestamps_opts [type: :utc_datetime]
end

end end

Now use MyApp.Schema to define new schemas

defmodule MyApp.Comment do use MyApp.Schema

schema "comments" do belongs_to :post, MyApp.Post

timestamps()

end end

2. Local configuration

defmodule ANV.Accounts.User do

  use Ecto.Schema

  # -- EITHER --------------------------
  @timestamps_opts [type: :utc_datetime]

  schema "users" do

    field :username, :string

    # -- OR -----------------------
    timestamps(type: :utc_datetime)
  end

Resources

  • lau/tzdata

    DateTime in Elixir "only handles "Etc/UTC" datetimes" but it can be configured with a custom time zone database, which is what the tzdata library is

+----------------------+------------------+------------------------+------------------------------+-----------------------------------+
|    Ecto 3 type       |    Elixir type   | Supports microseconds? | Supports DateTime functions? | Supports NaiveDateTime functions? |
+----------------------+------------------+------------------------+------------------------------+-----------------------------------+
| :utc_datetime_usec   | DateTime         |    YES                 |   YES                        |   YES                             |
| :utc_datetime        | DateTime         |    NO                  |   YES                        |   YES                             |
| :naive_datetime_usec | NaiveDateTime    |    YES                 |   NO                         |   YES                             |
| :naive_datetime      | NaiveDateTime    |    NO                  |   NO                         |   YES                             |
+----------------------+------------------+------------------------+------------------------------+-----------------------------------+