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]}
]
And that's how good old basic operations can save a day ;)
Kudos to peeps at #posgresql @ Freenode for explaining how to overcome those problems.
Top comments (0)