markusheilig

Ecto, myxql and zero-datetime

Hi everyone,

I have a table in a MySQL database with a ‘timestamp’ column called ‘created’ which may contain zero datetimes, i.e. ‘0000-00-00 00:00:00’.
My schema definition looks as follows:

defmodule MySchema do
  use Ecto.Schema

  schema "my_schema" do    
    field :created, :naive_datetime    
  end

end

I use the following dependencies (mix.exs) to access the database:

defp deps do
  [      
    {:ecto_sql, "~> 3.0"},
    {:myxql, git: "https://github.com/elixir-ecto/myxql.git", ref: "e01ebc7", override: true},
  ]
end

I use the e01ebc7 commit for myxql since it converts a zero datetime into an atom called ‘:zero_datetime’.

But when I try to query data using Repo.all(MySchema), I get the following error:

 (ArgumentError) cannot load `:zero_datetime` as type :naive_datetime for field :created in %MySchema{__meta__: #Ecto.Schema.Metadata<:loaded, "my_schema">, created: nil, id: nil }
    (ecto) lib/ecto/repo/queryable.ex:345: Ecto.Repo.Queryable.struct_load!/6
    (ecto) lib/ecto/repo/queryable.ex:201: anonymous fn/5 in Ecto.Repo.Queryable.preprocessor/3
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/ecto/repo/queryable.ex:158: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
    (my_app) lib/my_app/application.ex:23: MyApp.Application.example/0

Is there a way to convert data with :zero_datetime to MySchema?

Currently, I have to drop rows with zero-datetimes like this:

min_date = ~N[0000-01-01 00:00:00]
query = from m in MySchema,
        where: m.created >= ^min_date,
        select: m
Repo.all(query)

I’m looking forward for answers

11 comments

#ecto #mysql #datetime #myxql

0 3300 11

2021-08-31 09:56:27 UTC

Most Liked

dimitarvp

For future reference and for whomever might need this, here’s what worked for me:

defmodule YourApp.Types.ZeroableDateTime do
  use Ecto.Type

  def type, do: :naive_datetime

  def cast(data), do: {:ok, data}

  # Just return `nil` datetime instead of crashing.
  def load(:zero_datetime), do: {:ok, nil}
  def load(%NaiveDateTime{} = data), do: {:ok, data}

  def dump(%NaiveDateTime{} = data), do: {:ok, data}
  def dump(_), do: :error
end

Post #6

LostKobrakai

You can create a custom Ecto.Type implemetation, which knows how to handle zero’d dates, because 0000-00-00 00:00:00 is not a valid iso8601 datetime. There’s no month or day 0.

Post #2

wojtekmach

Hex Core Team

Yes, custom type is the way to go. I would however take a step back and ask what we are representing by this zero datetime? MySQL uses it to represent invalid values, eg result of some arithmetic, and only when appropriate sql mode is set, by default on newer versions you would get an error instead. This also brings the question that even if we add a custom type what would we cast/load/dump this value to - keep it as :zero_datetime? Then any time we work with this value we would have to check if its non-zero. Without knowing about the usecase I’d either use a NULL or smallest valid date instead of the zero one.

Post #3