Problem calling PosgreSQL interval function in Ecto query

Phoenix Forum Questions / Help
,
1

I have following query:

select * from shifts where date(shifts.starts_at + interval ‘7200 seconds’) > ‘2019-09-17’

where 7200 and date part are dynamic values calculated before query is executed. What I tried to do is to use fragment in the following way:

offset = #some calculation here from s in Shift, where: fragment("date(? + interval '? seconds') >= ?", s.starts_at, ^offset, ^Date.utc_today())

but I got error that type of parameter $2 cannot be determined. I’ve tried to use type(offset, :integer) as well as type(Integer.to_string(offset), :string) but none of them works. I’m obviously missing something but I cannot find a way to create such query through Ecto. Is something like this possible in Ecto?

2

Any reason to not use Ecto.Query.API.ago/2?

3 Likes
3

Indeed that works. Thanks! Small problem in some cases might be that Ecto.Query.API.ago/2 current time in UTC is not from DB. However resulting query revealed how fragment should be constructed and in case somebody needs it here it is:

fragment("date(? + (? * interval '1 second')) >= ?", s.starts_at, ^dst_offset, ^Date.utc_today())

The key was in multiplying PostgreSQL interval function with offset and not using offset as part of interval argument.

4

If you use :utc_datetime as your Ecto type in your schema then the time in the DB will have already been converted to UTC and you could use a more straightforward query.

1 Like