SQLite日期“大于其他日期”例如'25 / 09/2014'不行 [英] SQLite date "is greater than other date" e.g. '25/09/2014' doesn't work
问题描述
我有SQLite的问题，特别是我的日期。
如果我执行以下查询：
I've a problem with SQLite, and specially with my date. If I execute the following query :
SELECT g.Date FROM game w where g.date = "26/09/2014" order by timestamp desc
将给我
- 26/09/2014
- 26/09/2014
- 26/09/2014
但是，当我将查询项 = 更改为 >> ，日期为 25/09/2014
But when I change the query item = into > and the date into "25/09/2014"
SELECT distinct g.Date FROM gamew where g.date > "25/09/2014" order by timestamp desc
将给我以下结果我已经添加 - 不同）
- 26/09/2014
- 31 / 08/2014
- 30/08/2014
- 29/08/2014
- 28/08 / 2014
- 27/08/2014
- 26/08/2014
- 31/07/2014
- ...
- 26/09/2014
- 31/08/2014
- 30/08/2014
- 29/08/2014
- 28/08/2014
- 27/08/2014
- 26/08/2014
- 31/07/2014
- ...
哪些不是预期的结果:(因为只有一天是正确的
也
我真的不喜欢更改数据库，日期项目。我宁愿有一个正确的查询，给了我正确的基准回来
Which aren't the expecting results :( because, only the day is correct. also I really don't like to change the database, Date item. I rather like to have a correct query that gave me the correct datums back
示例数据库
example database
ID - Country - League - Team - Opponent - Location - Result - Scored - Against - Time - Date
--------------------------------------------------------------------------------
"20868","Spa","PRD","Celta_de_Vigo","Elche","A","1","1","0","1411758000","26/09/2014"
"47133","Ger","BUN","Hoffenheim","Mainz_05","A","x","0","0","1411756200","26/09/2014"
"59149","Ger","BUN","Mainz_05","Hoffenheim","H","x","0","0","1411756200","26/09/2014"
"25373","Spa","PRD","Cordoba","Valencia" ,"A","0","0","3","1411675200","25/09/2014"
... 11655 rows ...
修改
g.Date的类型：
Edit Type of g.Date:
DATE DATE
推荐答案
DD / MM / YYYY不是有效的SQLite日期。
DD/MM/YYYY is not a valid SQLite date.
SQLite没有实际的日期类型，并允许您将它们存储为TEXT，INT或REAL。如果您选择TEXT，您必须使用可以被SQLite的日期功能识别的格式YYYY-MM-DD，无论如何都可以解决您的排序和搜索问题。
SQLite doesn't have an actual date type and will allow you to store them as TEXT, INT, or REAL. If you choose TEXT you must use the format YYYY-MM-DD which can be recognized by SQLite's date functions and, in any event, will solve your sorting and searching problems.
这篇关于SQLite日期“大于其他日期”例如'25 / 09/2014'不行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
