Ecto query select records between two unix epoch

I am trying to to what I thought was a pretty basic ecto query to get the records from between two unix timestamps. Something along the lines of:

b = Ecto.DateTime.from_unix!(begin_utc_epoch, :second)
e = Ecto.DateTime.from_unix!(end_utc_epoch, :second)
query = from r in MyModel,
where r.inserted_at > ^b and r.inserted_at < ^e

I am getting nothing but errors. At the moment it is:

no function clause matching in Ecto.Type.cast_naive_datetime

I read somewhere that I should be using NaiveDateTime so have been trying to convert to naive time stamps. Any help would be appreciated. I am sure I am doing something wrong because it seems too complicated.

|> DateTime.to_naive


b = DateTime.from_unix!(begin_utc_epoch)|>DateTime.to_naive
e = DateTime.from_unix!(end_utc_epoch)|> DateTime.to_naive

Be careful of timezones etc.

grr I blindly trusted your module naming of Ecto.DateTime…


DateTime.from_unix!(1486035766, :second)|> DateTime.to_naive

1 Like

I found a way to do it in a relatively clean way:

b = NaiveDateTime.add(~N[1970-01-01 00:00:00], begin_utc_epoch)
e = NaiveDateTime.add(~N[1970-01-01 00:00:00], end_utc_epoch)
query = from r in MyModel,
    where r.inserted_at > ^b and r.inserted_at < ^e

what was messing me up is that I thought the timestamps in the query results where Ecto.DateTimes (did not know what the sigils ~N[blah blah]. They are NaiveTimeStamps. I was comparing apples to oranges. Doh!


You can use Kronos (with his driver)… but to be honest, I never test the ecto-driver …