帮助在SQLite中按日期排序结果 [英] Help ordering a result by date in SQLite
问题描述
例如:
SELECT * FROM details GROUP BY date;
John |史密斯2011年4月1日
John |史密斯2011年4月3日
John |史密斯2011年4月4日
John |史密斯2011年3月25日
三月份应该在四月份之前。
$ b $我猜想这里的答案是将我的日期存储为较长的时间戳,但是我不确定是否可以使用SQLite更容易地完成。
谢谢!
没有内置的 DATE
键入SQLite(如其他数据库管理系统中所存在),但它确实有一个很好的日期和时间函数: http://www.sqlite.org/lang_datefunc.html
您可以使用日期(2011年4月1日)返回ISO-8601日期(例如,2011-04-01)。
此格式具有可读字符串和可排序的优点。 2011-03-25自然是在2011-04-01之前通过标准字符串比较规则,所以没有必要的特殊操作。
所以,以这种格式存储你的日期,并使用 date()
函数(或其他相关函数)获取该格式。
Is there any way in SQLite to ORDER BY a date, and have result be ordered by time rather than alphabetically?
For example:
SELECT * FROM details GROUP BY date;
John | Smith | April 01, 2011
John | Smith | April 03, 2011
John | Smith | April 04, 2011
John | Smith | March 25, 2011
March should come before April.
I'm guessing that the answer here is to store my dates as long timestamps, however I wasn't sure if it could be done more easily with SQLite.
Thanks!
There isn't a built-in DATE
type in SQLite (as exists in some other database management systems), but it does have a nice complement of date and time functions: http://www.sqlite.org/lang_datefunc.html
You can use date("April 01, 2011") to get back an ISO-8601 date (e.g., 2011-04-01).
This format has the advantages of both being a readable string and being sortable. 2011-03-25 naturally comes before 2011-04-01 by standard string comparison rules, so there's no special operation required.
So, store your dates in that format, and get that format using the date()
function (or other relevant function).
这篇关于帮助在SQLite中按日期排序结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!