Ecto with fragment and now() use TimeZone


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.shift(hours: 1)
      |> Timex.to_naive_datetime()

    put_change(changset, :will_expire_at, will_expire_at)

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.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 ^ it works how it’s expected but still I would like to know where is the problem with fragment solution.

postgres NOW() returns with timezone (eg servers timezone - is it indeed UTC?) -

MyApp.Repo.query(“select NOW()::timestamp;”)
MyApp.Repo.query(“select timezone(‘UTC’, NOW())::timestamp;”)

your DB is most likely timestamp without time zone afaik (could be mistaken?) - so seems there would be a difference between how the the NOW() vs is being “cast” into fitting your DB structure…

Don’t know your DB stucture - your ecto schemas etc - and don’t know what is the expected output… but this should send you towards the solution/understanding…