Upgrading to Ecto 3. Anyway to easily deal with usec? It complains with or without `_usec`

If I use :utc_datetime it complains with :utc_datetime expects microseconds to be empty, got: #DateTime<2019-05-04 01:05:28.971099Z If I include _usec I get: :utc_datetime_usec expects microsecond precision, got: #DateTime<2019-05-08 07:00:00.000Z>. Is there anyway to drop usec on cast/load/dump globally? It’s not a new project, so there are rows in the db that may or may not have usec precision. I don’t care about the usec precision, I just want it to work without breaking.


The way we did it in our project was:

  1. Add this to your repo config (usually in config/<env>.exs:
config :your_app, YourApp.Repo,
  migration_timestamps: [type: :utc_datetime_usec],
  1. Put this at the top of your every Ecto schema file:
@timestamps_opts [type: :utc_datetime_usec]

Having to migrate to Ecto 3 due dependency issues…

On this note, if I have an existing migration that looks like this:

defmodule Foo.Repo.Migrations.CreateGlobalStatus do
  use Ecto.Migration

  def change do
    create table(:global_status) do
      add(:topic, :text)
      add(:highwater, :integer)
      add(:date_time, :utc_datetime)
      add(:payload, :string)

Am I supposed to just to a find & replace :utc_datetime -> :utc_datetime_usec in all of the files in priv/repo/migrations???

Or just change the schemas in modules that use :utc_datetime, e.g.

schema "global_status" do
    field(:topic, :string)
    field(:highwater, :integer)
    field(:date_time, :utc_datetime)
    field(:payload, :string)


schema "global_status" do
    field(:topic, :string)
    field(:highwater, :integer)
    field(:date_time, :utc_datetime_usec)
    field(:payload, :string)

Or both?

This is a production app, so I have no desire to change the actual DB tables!

The best then is to not change your migrations, you don’t want the dev DB to be different from the prod DB.

Ecto works fine with non-usec types, you just have to explicitly truncate timestamps when you build them:

date_time: NaiveDateTime.utc_now! |> NaiveDateTime.truncate(:second)

That should be it.



So I now get the error, it is a bit subtle.

The old utc_datetime supported a millisecond timestamp, e.g.:

#DateTime<2019-11-02 15:11:28.833Z>

Even though it was called microsecond.

In Ecto.Type, you are requiring either an empty :microsecond field, or exactly 6 digits of precision, therefore this bombs:

check_usec!(%{microsecond: {_, 6}} = datetime, _kind), do: datetime

whereas this would work:

check_usec!(%{microsecond: {_, 3}} = datetime, _kind), do: datetime

This is actually how the data is currently stored in the DB (a DateTime with 3 digits of precision), and likewise is also the result of DateTime.from_iso8601 below. (Note that I’m getting the timestamp string from a JSON api)

iex > {:ok, datetime, _} = DateTime.from_iso8601("2019-11-02T15:11:28.833Z")
iex > %{microsecond: {_, 3}} = datetime                                     
#DateTime<2019-11-02 15:11:28.833Z>
iex > %{microsecond: {_, 6}} = datetime
** (MatchError) no match of right hand side value: #DateTime<2019-11-02 15:11:28.833Z>

So, it seems like the gist of it is that there are no more millisecond DateTimes allow in Ecto (even though it’s a perfectly valid DateTime), just seconds (:utc_datetime) or 6 digit microseconds (:utc_datetime_usec).

Interesting. Not 100% sure I get the logic behind this decision to bomb on milliseconds. Why not just add three zeros at the end? Or allow 3 digits? Or have a :utc_datetime_msec type?

Maybe I’m missing something?

Just wondering, is there a reason not to add this to Ecto.Type?

defp check_usec!(%{microsecond: {microsecond, _}} = datetime, _kind) do
    if microsecond >= 0 and microsecond < 1_000_000 do
      raise ArgumentError,
            "#{inspect(kind)} expects microsecond precision, got: #{inspect(datetime)}"

The function pad_usec already exists in Ecto.Types:

defp pad_usec(%{microsecond: {microsecond, _}} = struct),
    do: %{struct | microsecond: {microsecond, 6}}

That way, if you either a) have data already stored as a usec but have less that 6 digits of precision, it works, and b) if you try to insert a valid DateTime with less than 6 digits it just cast it to 6 digits (e.g. zero pads).

Note, this error only happens if you’re using change, put_change etc, not on cast. These functions are supposed to work on internal data and so we’re extra strict there. The reason is, if you’d set ~U[2019-01-01 09:00:00.999999] as :utc_datetime field (which is timestamp(0) on Postgres), the database would actually save it as ~U[2019-01-01 09:00:01] and that’s what you’d get back which can lead to bugs. I’ve actually seen bugs in my test suite because of this off by 1 second. (They were flaky too because they happened roughly 50% of time, exactly whether the the microsecond was lesser or greater than .500000)

Note, such problem does not exist when casting, if you cast a date time with microseconds into a field without them, it would automatically be truncated (and the other way around too.)

TL;DR put_change/change/etc are stricter about data they accept.


I’m not sure about this addition exactly for the reasons in the previous comment that when working with internal data we want to avoid any casting and leave that to the user.

Worth mentioning that if the current rules don’t work for an app, one can always create their own type:

defmodule MyApp.UTCDateTime do
  @behaviour Ecto.Type

  def type(), do: :utc_datetime

  def cast(term), do: Ecto.Type.cast(:utc_datetime, term)

  def load(term), do: Ecto.Type.load(:utc_datetime, term)

  def dump(term) do
    # custom logic
1 Like

Ooops, had one insert that wasn’t using a changeset, but rather inserting the struct directly! All good now!



1 Like