Elixir, Ecto compare datetime in SQL query

Question

I have a problem with ecto query. I have this function:

def get_critials() do
  critical_time = DateTime.to_naive(Timex.shift(Timex.now, seconds: -600))
  query = "SELECT d.*"
          <> " FROM sc_devices AS d"
          <> " INNER JOIN log_device_commands AS ldc ON ldc.device_id = d.id"
          <> " WHERE ldc.inserted_at < timestamp '#{critical_time}'"

  {:ok, result} = Ecto.Adapters.SQL.query(Repo, query, [], [:rows])
  result.rows
end

What I want is to get all records from table sc_devices where column updated_at in log_device_commands is older than 600 seconds, but I get that output:

And I receive this output:

[
  [1, "LAMP 1XX_1", "1.st Lamp on the corner", 1,
   "6c7572e1-460f-43dd-b137-90c21d33525b", "XCA190SS2020DE", 3, 1, 1, 46.55472,
   15.64667, true, nil, ~N[2020-11-12 20:32:22.000000],
   ~N[2020-11-12 20:32:22.000000], 2],
  [1, "LAMP 1XX_1", "1.st Lamp on the corner", 1,
   "6c7572e1-460f-43dd-b137-90c21d33525b", "XCA190SS2020DE", 3, 1, 1, 46.55472,
   15.64667, true, nil, ~N[2020-11-12 20:32:22.000000],
   ~N[2020-11-12 20:32:22.000000], 2],
  [1, "LAMP 1XX_1", "1.st Lamp on the corner", 1,
   "6c7572e1-460f-43dd-b137-90c21d33525b", "XCA190SS2020DE", 3, 1, 1, 46.55472,
   15.64667, true, nil, ~N[2020-11-12 20:32:22.000000],
   ~N[2020-11-12 20:32:22.000000], 2]
]

Any ideas how can I solve that?

Answer 1

You could use postgres CURRENT_TIMESTAMP - INTERVAL '600 seconds' instead of using an elixir variable inside the query.

Also, I see you commented that you want to filter by updated_at but your query is actually filtering by inserted_at.