How to Interpolate tsrange in Ecto Fragment?

Questions / Help
1

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?

2

Try converting the timestamps to tuples using NaiveDateTime.to_erl/1. I came across a similar problem when using dateranges and Dates with Postgrex.Range.

3

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.

4

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
5

@michalmuskala that solved it, thanks!