如何在 Room 数据库中仅显示存储有特定年份日期的数据?

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

How to display only dates with a certain year stored in Room database?

问题

我使用MVVM架构,并将日期以类似12.05.2020的格式存储在数据库中。我想获取所有具有特定年份(例如2020年)的日期。
我当前的DAO查询如下:

@Query("SELECT * FROM Sorted WHERE date = :date")
LiveData<List<Sorted>> getSortedWhereDateIs(String date);

如何仅从完整的日期中提取年份或月份?谢谢任何帮助。

英文:

I use an MVVM structure and store dates in a database in a format like 12.05.2020. I want to get all the dates, that have a particular year, 2020 for example.
My current DAO query looks like:

@Query(&quot;SELECT * FROM Sorted WHERE date = :date&quot;)
LiveData&lt;List&lt;Sorted&gt;&gt; getSortedWhereDateIs(String date);

How can I pick out only year or month from a whole date? Thanks for any help.

答案1

得分: 1

检查这是否有效 -

  1. 从用户输入中提取年份
  2. 将年份传递给您的查询
@Query("SELECT * FROM Sorted WHERE YEAR(date) = :year")
LiveData<List<Sorted>> getSortedWhereDateIs(Integer year);

对于月份,您可以使用MONTH(fullDate)

英文:

Check if this works -

  1. Extract the year value from the user input
  2. Pass the year to your query
@Query(&quot;SELECT * FROM Sorted WHERE YEAR(date) = :year&quot;)
LiveData&lt;List&lt;Sorted&gt;&gt; getSortedWhereDateIs(Integer year);

For month you can use MONTH(fullDate)

答案2

得分: 1

你的查询应该类似于以下内容:

对于年份:

@Query("SELECT * FROM Sorted WHERE CAST(strftime('%Y', date / 1000,'unixepoch') AS INTEGER) = :yearId")

对于月份:

@Query("SELECT * FROM Sorted WHERE CAST(strftime('%m', date / 1000,'unixepoch') AS INTEGER) = :monthId")

更多细节可以查看这篇博客

英文:

Your query must be something like that:

 @Query(&quot;SELECT * FROM Sorted where CAST(strftime(&#39;%Y&#39;, date / 1000,&#39;unixepoch&#39;) as INTEGER)  = :yearId)

and for month:

@Query(&quot;SELECT * FROM Sorted where CAST(strftime(&#39;%m&#39;, date / 1000,&#39;unixepoch&#39;) AS INTEGER) = :monthId&quot;)

for more details you can check this blog

huangapple
  • 本文由 发表于 2020年5月3日 22:31:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/61576152.html
匿名

发表评论

匿名网友

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

确定