I have two custom type start_date
and end_date
which convert a date to the beginning of a day
or end of a day
# Start Date
defmodule Myapp.Ext.StartDate do
use Ecto.Type
def type, do: :timestamptz
@spec cast(%DateTime{}) :: {:ok, %DateTime{}}
def cast(%DateTime{} = dt) do
dt =
Timex.local(dt)
|> Timex.beginning_of_day()
|> Timex.to_datetime()
{:ok, dt}
end
@doc """
Sets microsecond to 0 to avoid Ecto changeset changing
due to microsecond difference
"""
@spec cast(String.t()) :: {:ok, %DateTime{}}
def cast(str) when is_binary(str) do
{:ok, dt} =
Timex.parse!(str, "{ISO:Extended:Z}")
|> cast()
{:ok, dt |> Timex.set(microsecond: {0, 0})}
end
@spec cast(any) :: :error
def cast(_), do: :error
@spec cast!(%DateTime{}) :: %DateTime{}
def cast!(%DateTime{} = dt) do
case cast(dt) do
{:ok, d} -> d
_ -> :error
end
end
@spec cast!(String.t()) :: %DateTime{}
def cast!(str) when is_binary(str) do
case cast(str) do
{:ok, d} -> d
_ -> :error
end
end
@spec load(tuple) :: {:ok, %DateTime{}}
def load({d, {h, i, s, _u}}) do
dt =
NaiveDateTime.from_erl!({d, {h, i, s}})
|> DateTime.from_naive!("Etc/UTC")
{:ok, dt}
end
def load(%DateTime{} = dt) do
{:ok, dt}
end
@spec load(any) :: :error
def load(_), do: :error
@spec dump(%DateTime{}) :: {:ok, %DateTime{}}
def dump(%DateTime{} = d) do
{:ok, d}
end
@spec dump({:ok, %DateTime{}}) :: {:ok, %DateTime{}}
def dump({:ok, %DateTime{} = d}) do
dump(d)
end
@spec dump(any) :: :error
def dump(_), do: :error
end
# End Date
defmodule Myapp.Ext.EndDate do
use Ecto.Type
def type, do: :timestamptz
@spec cast(%DateTime{}) :: {:ok, %DateTime{}}
def cast(%DateTime{} = dt) do
dt =
Timex.local(dt)
|> Timex.end_of_day()
|> Timex.to_datetime()
{:ok, dt}
end
@doc """
Sets microsecond to 0 to avoid Ecto changeset changing
due to microsecond difference
"""
@spec cast(String.t()) :: {:ok, %DateTime{}}
def cast(str) when is_binary(str) do
{:ok, dt} =
Timex.parse!(str, "{ISO:Extended:Z}")
|> cast()
{:ok, dt |> Timex.set(microsecond: {0, 0})}
end
@spec cast(any) :: :error
def cast(_), do: :error
@spec cast!(%DateTime{}) :: %DateTime{}
def cast!(%DateTime{} = dt) do
case cast(dt) do
{:ok, d} -> d
_ -> :error
end
end
@spec cast!(String.t()) :: %DateTime{}
def cast!(str) when is_binary(str) do
case cast(str) do
{:ok, d} -> d
_ -> :error
end
end
@spec load(tuple) :: {:ok, %DateTime{}}
def load({d, {h, i, s, _u}}) do
dt =
NaiveDateTime.from_erl!({d, {h, i, s}})
|> DateTime.from_naive!("Etc/UTC")
{:ok, dt}
end
def load(%DateTime{} = dt) do
{:ok, dt}
end
@spec load(any) :: :error
def load(_), do: :error
@spec dump(%DateTime{}) :: {:ok, %DateTime{}}
def dump(%DateTime{} = d) do
{:ok, d}
end
@spec dump({:ok, %DateTime{}}) :: {:ok, %DateTime{}}
def dump({:ok, %DateTime{} = d}) do
dump(d)
end
@spec dump(any) :: :error
def dump(_), do: :error
end
Given a schema
@timestamps_opts [type: :utc_datetime_usec]
schema "images" do
field(:start_date, Myapp.Ext.StartDate)
field(:end_date, Myapp.Ext.EndDate)
timestamps()
end
And a query
local_now = Timex.local() # say, 2021-01-31 10:59:59.999999Z
from(
c in Image,
where: c.status == true,
where: c.end_date >= ^local_now,
where: c.start_date <= ^local_now,
)
It would actually produce
SELECT *
FROM "images" AS c0
WHERE (c0."end_date" >= $1) AND (c0."start_date" <= $2)
LIMIT $3 OFFSET $4
[~U[2021-01-31 12:59:59.999999Z], ~U[2021-01-30 13:00:00.000000Z], 1000, 0]
The variable localnow
actually got converted to 2021-01-30 13:00:00.000000Z
by StartDate
and to 2021-01-31 12:59:59.999999Z
by EndDate
.
I have been using these two custom types since Ecto 2
which worked fine. And I found that the behaviour has been changed Ecto 3
.
I have checked the Ecto changelog and the latest doc about custom type and I couldn’t find anything related to this.
update 1: it looks like Ecto behaves like this in Ecto 2 already. so I have this bug all the time from day 1.
Did I miss anything?