How do I store a Unix timestamp in Ecto?

Yep, never compare Date/Time/DateTime/NaiveDateTime or any struct at all with == equality testing. If they are designed to be comparable then either they should tell you that == works or they expose something like a compare function on their modules (which Date/Time/DateTime/NaiveDateTime do), so you need to compare Date/Time/DateTime/NaiveDateTime with the appropriate compare call on the appropriate module type.

OK, the last part here is still troublesome if someone has a solution.

My tests use == to check the existing vs the fixture (generated code).
I am passing in ~N[2018-02-18 13:31:08]) thanks to the above custom type.
But postgres has more precision than that, and returns ~N[2018-02-18 13:31:08.000000]
So the tests fail on the 0s in the milli and microsecond parts.

Reading the docs on NaiveDateTime == uses struct comparison, so the values are not strictly equivalent. Example:

iex(43)> NaiveDateTime.compare(~N[2018-02-18 13:31:08.000000], ~N[2018-02-18 13:31:08])
:eq
iex(44)> ~N[2018-02-18 13:31:08.000000] == ~N[2018-02-18 13:31:08]                     
false

I am doing something awful, and only right in my one use case as I limit precision, but it works and I can’t spend more time on a single date field.

  def load(value) do
    {:ok, result} = Ecto.Type.load(:naive_datetime, value)
    result = NaiveDateTime.truncate(result, :second)

    {:ok, result}
  end

If there is a better way to solve this that would be awesome, but thanks to @OvermindDL1 for getting me this far.

I also have to wonder if I am off the beaten path here. Does no one else do this or do they convert values at a higher level in their apps?

OK, I have both versions working, and will paste here in case someone else has this same issue.

Short of it, cast does work:

defmodule UnixTimestamp do
  @behaviour Ecto.Type
  def type, do: :naive_datetime

  def cast(timestamp) when is_integer(timestamp) do
    case DateTime.from_unix(timestamp) do
      {:ok, date} -> {:ok, DateTime.to_naive(date)}
      {:error, reason} -> {:error, reason}
    end
  end

  def cast(_), do: :error

  def dump(value), do: Ecto.Type.dump(:naive_datetime, value)

  def load(value), do: Ecto.Type.load(:naive_datetime, value)
end

and my migration uses naive_datetime so the field is like the timestamps.

Source:

At the bottomish of the second article it has an example of implementing the type.

Right, I read those, but, “Ecto native type” doesn’t help me. It’s a DateTime, but from_date wants an Ecto.Date not a DateTime. I guess cast, but that only takes binaries, maps, tuples, and Ecto.Dates.

https://hexdocs.pm/ecto/Ecto.DateTime.html#from_date/1

For making your own type it’s documented at:

Specifically:

Thanks for the reply.

I tried this both ways with varying degrees of success / unsuccess. I’ll leave what I did here in case it helps someone else / is worth figuring out.

#custom cast##
This way worked, except the assert on the original incoming data fails as the version from pg has a different precision.

#DateTime<2018-02-18 13:31:08.000000Z
vs
#DateTime<2018-02-18 13:31:08Z

  defp cast_timestamp(%Ecto.Changeset{} = changeset, fields) when is_list(fields) do
    Enum.reduce(fields, changeset, fn(field, acc) -> cast_timestamp(acc, field) end)
  end

  defp cast_timestamp(%Ecto.Changeset{} = changeset, field) when is_atom(field) do
    changeset.params
    |> Map.get(to_string(field))
    |> DateTime.from_unix()
    |> case do
      {:ok, datetime} -> put_change(changeset, field, datetime)
      {:error, reason} -> add_error(changeset, field, reason)
    end
  end

#custom type##
I had less success this way, as I have no idea what dump wants in order to store the value correctly.

defmodule UnixTimestamp do
  @behaviour Ecto.Type
  def type, do: :utc_datetime

  def cast(timestamp) when is_integer(timestamp) do
    # returns result ok/error tuple, great
    DateTime.from_unix(timestamp)
  end

  def cast(_), do: :error

  def load(datetime) do
    # I guess?
    datetime
  end

  def dump(datetime) do
    # no idea what you want from me
    IO.inspect(datetime)
    {:ok, datetime}
  end

  def dump(_), do: :error
end

You just need to DateTime.from_unix! (or no ! if you want to handle invalid input) before passing it to the database. The utc_datetime type is entirely the appropriate type to use but it needs a (Naive)DateTime structure.

You can make a custom field type too (name it UnixTimestamp or so? ^.^).