Manipulating INTERVALs in Ecto fragments

#ecto #postgres #phoenix #elixir

Parametarizing INTERVAL units

If you ever had to come across a task of selecting entries for last N hours/days/month you're probably already familiar with ERROR 22007 (invalid_datetime_format) invalid input syntax for type interval coming right your way from PostgreSQL.

Let's work it out on an example of selecting all emails from users that signed up during the last day or month.

A rough translation of such SQL query to Ecto can look like this

period = "day"

Repo.all(
  from(u in "users",
    select: {u.email, u.inserted_at},,
    where: u.signup_date > fragment("CURRENT_DATE - INTERVAL '1 ?'", ^period)
  )
)

As far as I'm acknowledged the reason for invalid_datetime_format error to happen is that parameter substitution is not allowed in string literals. Instead, we can rewrite the query to use a very neat trick of string concatenation to combine interval parts into a single string

period = "day"

Repo.all(
  from(u in "users",
    select: {u.email, u.inserted_at},,
    where: u.signup_date > fragment("CURRENT_DATE - ('1 ' || ?)::interval", ^period)
  )
)

voila

[
  {"text@exampe.com", ~N[2020-04-08 09:31:02.000000]},
  {"test2@example.com", ~N[2020-04-08 09:34:42.000000]}
]

Parametarizing number of INTERVAL units

Now, let's imagine that we want to parameterize the number of intervals - go from "last day/month" to "last N days/months".

Again, we know that it's impossible to do fragment("CURRENT_DATE - INTERVAL '? ?'", ^amount, ^period). Why not circumvent this limitation with multiplication?

amount = 3
period = "day"

Repo.all(
  from(u in "users",
    select: {u.email, u.inserted_at},
    where: u.signup_date > fragment("CURRENT_DATE - ('1 ' || ?)::interval * ?", ^period, ^amount)
  )
)

which gives us

[
  {"test3@example.com", ~N[2020-04-06 12:07:20.000000]},
  {"text@exampe.com", ~N[2020-04-08 09:31:02.000000]},
  {"test2@example.com", ~N[2020-04-08 09:34:42.000000]}
]