How do I store a Unix timestamp in Ecto?

I have a timestamp coming back from a 3rd party package. I have read these articles and don’t see how to just store this value in my db and have it come back as some sort of date type in elixir.

  1. is there an Ecto type that should just take in the timestamp and convert it?
  2. do I need to convert it manually in the changeset? (that would seem strange)

The codes:

##My Timestamp##
DateTime.from_unix 1518960668
Yeah this is cool

##using :utc_datetime##

** (FunctionClauseError) no function clause matching in Ecto.Type.cast_naive_datetime/1

The following arguments were given to Ecto.Type.cast_naive_datetime/1:

    # 1
    1518960668

Attempted function clauses (showing 4 out of 4):

    defp cast_naive_datetime(binary) when is_binary(binary)
    defp cast_naive_datetime(%{"year" => empty, "month" => empty, "day" => empty, "hour" => empty, "minute" => empty}) when empty === nil or empty === ""
    defp cast_naive_datetime(%{year: empty, month: empty, day: empty, hour: empty, minute: empty}) when empty === nil or empty === ""
    defp cast_naive_datetime(%{} = map)

code: token = token_fixture()

##using :time##

changeset errors:

...
errors: [expires_at: {"is invalid", [type: :time, validation: :cast]}]

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. :slight_smile:

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

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

For making your own type it’s documented at:

Specifically:

:slight_smile:

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

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.

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?

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. :slight_smile: