I am trying to create a query using a fragment to compare a postgres tsrange
. The code is
Repo.all(
from m in Model,
where: fragment(
"?::tsrange @> ?::timestamp",
^%Postgrex.Range{lower: ~N[2018-01-01 00:00:00], upper: ~N[2019-01-01 00:00:00]},
m.timestamp
)
)
The schema defines the timestamp
field as :naive_datetime
.
The error I get is
** (ArgumentError) could not encode date/time: ~N[2018-01-01 00:00:00]
This error happens when you are by-passing Ecto's Query API by
using either Ecto.Adapters.SQL.query/4 or Ecto fragments. This
makes Ecto unable to properly cast the type. You can fix this by
explicitly telling Ecto which type to use via `type/2` or by
implementing the Ecto.DataType protocol for the given value.
I’ve experimented with other fields in the schema and I’m able to query just fine (using ::numrange/::numeric
for example). Is this something specific to timestamp ranges?
Try converting the timestamps to tuples using NaiveDateTime.to_erl/1
. I came across a similar problem when using daterange
s and Date
s with Postgrex.Range
.
I get similar errors when I do that or use a string in place of the range ("'[2018-01-01 00:00:00, 2019-01-01 00:00:00)'"
). The best I’ve been able to do is split up the range query into it’s lower
and upper
and then make 2 conditionals (?::timestamp < ? and ? < ?::timestamp
), but that kind of beats the purpose of using a range.
I usually use the range constructor functions when working with ranges, so tsrange
, daterange
, etc.
After wrapping them with macros, it becomes quite convenient to use:
defmacro tsrange(lower, upper) do
# there's a third optional argument for inclusive/exclusive bounds, defaulting to '[)'
quote(do: fragment("tsrange(?, ?)", unquote(lower), unquote(upper)))
end
defmacro overlaps(range, value) do
quote(do: fragment("? @> ?", unquote(range), unquote(value)))
end
This allows you to use something like:
from m in Model,
where: overlaps(tsrange(~N[2018-01-01 00:00:00], ~N[2018-01-01 00:00:00]), m.timestamp)
3 Likes
@michalmuskala that solved it, thanks!