CODEWITHSUNDEEP
postgresqlelixirecto
gosseti
gosseti

Reputation: 975

Ecto query the difference between two dates in minutes

I have a query where I would like to find Booking records that have more than 1500 minutes between when they were inserted and when the booking starts.

The idea is this:

booking.start - booking.inserted_at > 1500 minutes

The number of minutes may vary, so this is a variable (type integer). Currently I am trying this:

# this will be a dynamic integer
mins_diff = 1500

from(b in Booking)
|> where(
  [b],
  fragment("? - ? > interval '? minutes'", b.start, b.inserted_at, ^mins_diff)
)
|> Repo.all()

But it is throwing errors saying:

** (ArgumentError) parameters must be of length 1 for query %Postgrex.Query{}
# rest of error truncated (because it is massive)

Upvotes: 0

Views: 763

Answers (1)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 120990

This is weird, but I can confirm the parameter is not handled correctly by Ecto.Query.API.fragment/1 if passed that way.

Here is a workaround.

from b in Booking,
     where: fragment(
              "? - ? > ? * interval '1 minute'",
              b.start,
              b.inserted_at,
              ^mins_diff
            )

Upvotes: 2

Related Questions