How to query for dates or date range

Question

I am trying to make possible to filter products by their insertion date. It can be a single date as a from/to date of filtering or a range from starting to ending date from the form. I'm unsure if I'm even close to make it work because of errors I'm getting every time I change something in my query.

I would appreciate any hints on how I can improve my code and also how to make it work.

My form looks like that:

<%= form_for @conn, Routes.products_path(@conn, :index), [method: :get, as: :search, class: "ml-2 row", page_size: @page.page_size, page: @page.page_number], fn f -> %>
  <div class="col-12 align-items-end row">
    <label class="form-label col-1">
      <%= search_input f, :start_date,  class: "form-control", placeholder: "From" %>
    </label>
    <label class="form-label col-1">
      <%= search_input f, :end_date,  class: "form-control", placeholder: "To" %>
    </label>
    <label class="form-label col-1">
      <%= submit "Search", class: "btn btn-primary" %>
    </label>
  </div>
<% end %>

My index function in a controller:

 products = ProductsRepo.get_products_by_insertion_date(start_date, end_date)

  page =
    products
    |> ProductsRepo.paginate()
  render(conn, "index.html", products: page.entries, page: page)

And finally query I'm trying to get to work:

  def get_products_by_insertion_date(nil, nil) do
    Products
  end

  def get_products_by_insertion_date(start_date, nil) do
    from(p in Products, where: p.inserted_at >= ^start_date)

  end

  def get_products_by_insertion_date(nil, end_date) do
    from(p in Products, where: p.inserted_at <= ^end_date)
  end

  def get_products_by_insertion_date(start_date, end_date) do
    from(p in Products,
    where: p.inserted_at >= ^start_date and
    p.inserted_at <= ^end_date
    )
  end

Currently I get an error:

value "2023-01-13" in where cannot be cast to type :utc_datetime in query:

Answer 1

I think this is just a matter of converting the supplied input into a viable DateTime. The submitted value is a string, so you have to convert that to a DateTime. You can rely on a library such as timex to do it, or you can manually deconstruct the string, e.g.

iex> [yyyy, mm, dd] = String.split("2023-01-13", "-")
["2023", "01", "13"]
iex> date = Date.new!(String.to_integer(yyyy), String.to_integer(mm), String.to_integer(dd))
~D[2023-01-13]
iex> datetime = DateTime.new!(date, Time.new!(0, 0, 0))
~U[2023-01-13 00:00:00Z]

Or you can use Calendar.ISO.parse_date/1 to retrieve the components:

iex> {:ok, {yyyy, mm, dd}} = Calendar.ISO.parse_date("2023-01-13")
{:ok, {2023, 1, 13}}
iex> date = Date.new!(yyyy, mm, dd)
~D[2023-01-13]
iex> datetime = DateTime.new!(date, Time.new!(0, 0, 0))
~U[2023-01-13 00:00:00Z]

Once you have a viable %DateTime{} struct, you should be able to issue your query normally.