Ecto type that maps to postgresql's timetz (time with a time zone)

Questions / Help
, ,
1

I’m implementing an Ecto schema with a field that maps to postgresql’s timetz type (time with a time zone).

I understand that there is no built in type that maps to timetz so I have to create my own custom Ecto type for this field. I’ve gone ahead and started doing this following this guide on hexdocs

defmodule EctoTimetz do
  use Ecto.Type

  def type, do: :timetz

  def cast(time) when is_binary(time) do
    IO.puts("hi from cast")
    {:ok, Timetz.new(time)}
  end

  def cast(%Timetz{} = time), do: {:ok, time}

  def cast(_), do: :error

  def load(data) do
    IO.puts("hi from load")
    IO.inspect(data)
    {:ok, Timetz.new(data)}
  end

  def dump(%Timetz{} = time) do
    IO.puts("hi from dump")
    IO.inspect(time)
    {:ok, time}
  end

  def dump(_), do: :error
end

The field is declared in my schema like so

field :due_at, EctoTimetz

The problem I’m running into is the underlying database adapter seems to convert the field to a Time object before I ever see it, which drops the timezone.

For example, I’ve got a field in my database with a valid timetz of 22:00:00-05. When I load it into ecto like so

Repo.get(Assignment.Schema, 1)

In my console, I see something like this:

hi from load
~T[03:00:00.000000]

So somewhere in a lower layer, the time is being converted into a Time object in UTC time, and the actual timezone info is being dropped. I need that timezone data in my phoenix app layer.

What am I doing wrong?

Thanks!

2

Remember that PostgreSQL do not store TZ in the DB (even with TIMETZ). You probably do not want to use it or you want to store TZ on your own in separate column or as a composite type.

2 Likes