SQLite Order By Date1530019888000
SqlSqliteAndroid SqliteSql Order-BySql Problem Overview
Every record in my SQLite database contains a field which contains a Date stored as a string in the format 'yyyy-MM-dd HH:mm:ss'.
Is it possible to query the database to get the record which contains the most recent date please?
Sql Solutions
Solution 1 - Sql
you can do it like this
SELECT * FROM Table ORDER BY date(dateColumn) DESC Limit 1
Solution 2 - Sql
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
Solution 3 - Sql
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:
SELECT * FROM Table ORDER BY dateColumn DESC Limit 1
Without any extra date function!
Solution 4 - Sql
You need to convert it to unix timestamp, and then compare them:
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.
Solution 5 - Sql
You can convert your column sent_date_time to yyyy-MM-dd format and then order by date:
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
Solution 6 - Sql
In my case everything works fine without casting column to type 'date'. Just by specifying column name with double quotes 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.
Solution 7 - Sql
You can also use the following query
"SELECT * FROM Table ORDER BY strftime('%Y-%m-%d %H:%M:%S'," + dateColumn + ") DESC Limit 1"
Solution 8 - Sql
I found this ugly hack worked.
select *, substr(date_col_name,7,4)as yy, substr(date_col_name,4,2) as mm, substr(date_col_name,1,2) as dd from my_table order by yy desc,mm desc,dd desc
it would be better to convert the text column to date field type, but I found that did not work reliably for me.
Solution 9 - Sql
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)
Then it's easy to sort, compare, etc ...
Solution 10 - Sql
This will work for both date and time
SELECT *
FROM Table
ORDER BY
julianday(dateColumn)
DESC Limit 1