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
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 . 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.
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.
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)
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!