Functions used in db field

Is there a way to use function in db field while making query? Ex.

from(u in User)
|> where([u], (u.inserted_at |> Timex.local) > ^input_date )
|> Repo.all

Not in the way you are attempting. However in this case just using where([u], u.inserted_at > ^input_date) should work as expected. Though, you may need to convert the date into a DateTime first.

doesn’t work as expected :frowning: . If input_date (datetime) is 2017-01-20 00:00:00, the query will return inserted_at 2017-01-20 01:00:00. But, with correct timezone, this inserted_at value is 2017-01-19 23:00:00 and should be rejected.

Then you need to make sure that your input_date has the correct timezone associated with it before making the query.

1 Like

Do you know a smart way to convert a date 2017-01-01 (the input) to datetime in utc 2017-01-01 02:00:00 ?

Since you appear to be using timex already, input_date |> Date.from_iso8601!() |> Timex.to_datetime(timezone). Depending on your needs, that should be good.

2 Likes

And you can also use .from_iso8601 for a DateTime directly,

{:ok, datetime, 0} = DateTime.from_iso8601("#{year}-#{String.pad_leading(month, 2, "0")}-#{String.pad_leading(day, 2, "0")}T#{hhmmss}Z")

(but just use Timex as suggested by Ankhers)

2 Likes

Nice! I didn’t know about the timezone parameter of to_datetime. I had to use Timex.to_datetime after this expression to convert in UTC.

Anyway, this fix the problem apparently! Thanks a lot!