How do I store a Unix timestamp in Ecto?

Questions / Help
,
1

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.

https://hexdocs.pm/ecto/Ecto.Schema.html

  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]}]
2

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? ^.^).

3

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
4

For making your own type it’s documented at:
https://hexdocs.pm/ecto/Ecto.Type.html

Specifically:
https://hexdocs.pm/ecto/Ecto.Type.html#c:dump/1
https://hexdocs.pm/ecto/Ecto.Type.html#c:load/1

:slight_smile:

1 Like
5

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

6

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.

2 Likes
7

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?

8

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: