I'm using Ecto to make queries on a postgres db.
The problem is that db has fields with utc datetime, but I need that queries convert this data to the local timezone before comparison.
from(u in User) |> where([u], u.created_at < ^input_datetime) |> Repo.all
The above expression will consider created_at in utc format to compare. I dont want this. I want the Ecto convert created_at to local timezone before comparison to return correct result.
Is there a way to do this?
You will need to convert the input_date to the proper timezone before making the query.
input_date
|> Date.from_iso8601!()
|> Timex.to_datetime(timezone)
|> Timex.to_datetime()
That should convert the input date to whichever timezone you want to compare it to, and then convert it to the UTC representation of that datetime in order to compare in the ecto query.
Note that the only reason you need to convert it to UTC yourself is because the timestamps (inserted_at and updated_at) are by default the :naive_datetime type. If they were :utc_datetime, it would have done the conversion for you.
(full conversation here: https://elixirforum.com/t/functions-used-in-db-field/12673)