Hello,
so we have these versions:
ecto 3.2.3
ecto_sql 3.2.0
phoenix_ecto 4.0.0
postgrex 0.15.1
posgres server 11.5/10.5
We have some model which should expire 1 hour after it’s created. We created attribute will_expire_at and fill it with +1 hour logic when we create this entry:
defp set_expiration(changset) do
will_expire_at =
Timex.now()
|> Timex.shift(hours: 1)
|> Timex.to_naive_datetime()
put_change(changset, :will_expire_at, will_expire_at)
end
Data in DB are good and all timestamps are right.
Our DB and Elixir has UTC TimeZone. I execute query directly in DB I get this:
SELECT NOW(), f0.id, f0.will_expire_at FROM some_table AS f0 WHERE ((f0.will_expire_at <= NOW()) AND (f0.status = 0));
=> Zero rows
When I call this in console/app:
from(pi in SomeTable , where: pi.will_expire_at <= fragment("NOW()") and pi.status == ^:processing)
|> Repo.all()
It returns one row. I executed query directly from debug log also and it returns zero entries.
That one entry will expire for one our later. So looks like when Ecto call fragment with function now() it somehow apply timezone on session with psql. Because when I call
MyApp.Repo.query("SELECT now();")
it returns time without timezone, so same what we have in DB and Elixir.
So problem is with query. This query (somehow) generate results for now + 1 hour instead of only now. Any ideas where can be problem? I tested it on localhost(mac), kubernetes pod (alpine) with 2 different psql servers.
When I changed fragment("NOW()") into ^Timex.now() it works how it’s expected but still I would like to know where is the problem with fragment solution.
