How to get records of just last week using ecto

Question

MyApp.Order
    |> where([m], m.inserted_at > datetime_add(^Ecto.DateTime.utc, -2, "week"))
    |> where([m], m.inserted_at < datetime_add(^Ecto.DateTime.utc, 0, "week"))

datetime_add(^Ecto.DateTime.utc, 0, "week") does not seem to make it as current week. So, this query fetches record from last week and current week.

Alternatively,

  defp weekly_orders do
    {interval1, interval2} = week_intervals
    MyApp.Order
    |> where([m], m.inserted_at >= datetime_add(^Ecto.DateTime.utc, ^interval1, "day"))
    |> where([m], m.inserted_at < datetime_add(^Ecto.DateTime.utc, ^interval2, "day"))
  end

  defp week_intervals do
    {-(day_of_the_week + 6) , -(day_of_the_week - 1)}
  end
  defp day_of_the_week do
    :erlang.date
    |> :calendar.day_of_the_week
  end

This fails on Monday because datetime_add(Ecto.DateTime.utc, 0, "day")

1. What does datetime_add(Ecto.DateTime.utc, 0, "day") return or denote?
2. What's the right way to query for records of just last week using ecto?

Answer 1

datetime_add(Ecto.DateTime.utc, 0, "day") adds 0 days to the current datetime, so it basically does nothing.

I'd use dates here instead of datetimes, as you're really more interested in just days of the week. so maybe something like

distance_to_monday = -(:erlang.date |> :calendar.day_of_the_week) + 1
distance_to_sunday = 7 - (:erlang.date |> :calendar.day_of_the_week)

MyApp.Order
  |> where([m], m.inserted_at >= date_add(^Ecto.Date.utc, ^distance_to_monday, "day"))
  |> where([m], m.inserted_at <= date_add(^Ecto.Date.utc, ^distance_to_sunday, "day")

Answer 2

You need to use Timex

 room_messages = Repo.all(
        from m in Message,
        where: m.inserted_at >= ^Timex.beginning_of_day(Timex.now), where: 
        m.inserted_at <= ^Timex.end_of_day(Timex.now),

      )

Answer 3

Few years later, would also use Timex and do something like:

MyApp.Order
|> where([m], m.inserted_at >= ^Timex.subtract(Timex.now, Timex.Duration.from_days(7)))