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

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.

3 Likes

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.

2 Likes

I already read about Ecto’s custom types but I wasn’t completely sure if this is the way to go.
Thanks very much for your answers!