英文:
How to query for dates or date range
问题
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.
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, the 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:
英文:
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:
答案1
得分: 3
我认为这只是将提供的输入转换为可用的DateTime
的问题。提交的值是一个字符串,因此您需要将其转换为DateTime
。您可以依赖于像timex
这样的库来执行此操作,或者您可以手动拆解字符串,例如:
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]
或者,您可以使用Calendar.ISO.parse_date/1
来检索组件:
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]
一旦您拥有可用的%DateTime{}
结构,您应该能够正常发出查询。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论