SQL SQLite 按日期排序1530019888000
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14091183/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite Order By Date1530019888000
提问by duncanportelli
Every record in my SQLitedatabase contains a field which contains a Datestored as a stringin the format 'yyyy-MM-dd HH:mm:ss'.
在我的每一个记录SQLite数据库包含其中包含了一个字段Date存储的string格式'yyyy-MM-dd HH:mm:ss'。
Is it possible to query the database to get the record which contains the most recent date please?
是否可以查询数据库以获取包含最近日期的记录？
回答by Paritosh
you can do it like this
你可以这样做
SELECT * FROM Table ORDER BY date(dateColumn) DESC Limit 1
回答by Ahmad Baraka
For me I had my query this way to solve my problem
对我来说，我通过这种方式查询来解决我的问题
select * from Table order by datetime(datetimeColumn) DESC LIMIT 1
Since I was storing it as datetime not date column
因为我将它存储为日期时间而不是日期列
回答by Nabi K.A.Z.
When you sure the format of text field is yyyy-MM-dd HH:mm:ss(ex.: 2017-01-02 16:02:55), So It works for me simply:
当您确定文本字段的格式是yyyy-MM-dd HH:mm:ss（例如：）时2017-01-02 16:02:55，它对我来说很简单：
SELECT * FROM Table ORDER BY dateColumn DESC Limit 1
SELECT * FROM Table ORDER BY dateColumn DESC Limit 1
Without any extra date function!
没有任何额外的日期功能！
回答by Zaffy
You need to convert it to unix timestamp, and then compare them:
您需要将其转换为unix时间戳，然后进行比较：
SELECT * FROM data ORDER BY strftime('%s', date_column) DESC
But this can be pretty slow, if there are lots of rows. Better approach would be to store unix timestamp by default, and create an index for that column.
但是如果有很多行，这可能会很慢。更好的方法是默认存储 unix 时间戳，并为该列创建索引。
回答by bpatel
You can convert your column sent_date_timeto yyyy-MM-ddformat and then order by date:
您可以将列转换sent_date_time为yyyy-MM-dd格式，然后按日期排序：
1) substr(sent_date_time,7,4)||"-"||substr(sent_date_time,1,2)||"-"||substr(sent_date_time,4,2) as date
2) order by date desc
回答by user3765475
In my case everything works fine withoutcastingcolumn to type 'date'. Just by specifying column name with doublequotes like that:
在我的情况下，一切正常，无需将列转换为“日期”。只需用双引号指定列名，如下所示：
SELECT * FROM 'Repair' ORDER BY "Date" DESC;
I think SQLite makes casting by itself or something like that, but when I tried to 'cast' Date column by myself it's not worked. And there was no error messages.
我认为 SQLite 可以自己进行转换或类似的操作，但是当我尝试自己“转换”日期列时，它不起作用。并且没有错误消息。
回答by kofifus
If you do a lot of date sorting/comparison, you may get better results by storing time as ticks rather than strings, here is showing how to get 'now' in ticks with:
如果您进行大量日期排序/比较，通过将时间存储为刻度而不是字符串，您可能会获得更好的结果，这里展示了如何在刻度中获取“现在”：
((strftime('%s', 'now') - strftime('%S', 'now') + strftime('%f', 'now')) * 1000)
(see https://stackoverflow.com/a/20478329/460084)
（见https://stackoverflow.com/a/20478329/460084）
Then it's easy to sort, compare, etc ...
然后很容易排序，比较等......
回答by ZIRES
You can also use the following query
您还可以使用以下查询
"SELECT * FROM Table ORDER BY strftime('%Y-%m-%d %H:%M:%S'," + dateColumn + ") DESC Limit 1"
回答by Venkatesh masanolla
This will work for both date and time
这将适用于日期和时间
SELECT *
FROM Table
ORDER BY
julianday(dateColumn)
DESC Limit 1
