如何查询日期或日期范围

huangapple go评论58阅读模式
英文:

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:

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

My index function in a controller:

 products = ProductsRepo.get_products_by_insertion_date(start_date, end_date)

  page =
    products
    |&gt; ProductsRepo.paginate()
  render(conn, &quot;index.html&quot;, 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 &gt;= ^start_date)

  end

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

  def get_products_by_insertion_date(start_date, end_date) do
    from(p in Products,
    where: p.inserted_at &gt;= ^start_date and
    p.inserted_at &lt;= ^end_date
    )
  end

Currently I get an error:

> value &quot;2023-01-13&quot; in where cannot be cast to type :utc_datetime in query:

答案1

得分: 3

我认为这只是将提供的输入转换为可用的DateTime的问题。提交的值是一个字符串,因此您需要将其转换为DateTime。您可以依赖于像timex这样的库来执行此操作,或者您可以手动拆解字符串,例如:

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

或者,您可以使用Calendar.ISO.parse_date/1来检索组件:

iex&gt; {:ok, {yyyy, mm, dd}} = Calendar.ISO.parse_date("2023-01-13")
{:ok, {2023, 1, 13}}
iex&gt; date = Date.new!(yyyy, mm, dd)
~D[2023-01-13]
iex&gt; 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&gt; [yyyy, mm, dd] = String.split(&quot;2023-01-13&quot;, &quot;-&quot;)
[&quot;2023&quot;, &quot;01&quot;, &quot;13&quot;]
iex&gt; date = Date.new!(String.to_integer(yyyy), String.to_integer(mm), String.to_integer(dd))
~D[2023-01-13]
iex&gt; 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&gt; {:ok, {yyyy, mm, dd}} = Calendar.ISO.parse_date(&quot;2023-01-13&quot;)
{:ok, {2023, 1, 13}}
iex&gt; date = Date.new!(yyyy, mm, dd)
~D[2023-01-13]
iex&gt; 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.

huangapple
  • 本文由 发表于 2023年3月7日 02:55:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/75654751.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定