How to perform DESC table sort on dates stored as TEXT type.

[hammer]

SQLite does not offer a Date type in the drop down that i could find. I think i read to use TEXT type for dates. When i return a query using DESC for this field it almost sorts correctly but for instance, 4-5-2022 is listed before 4-11-2022.
Will i have to do the sorting of the returned list outside the query?
If so is there a standard way of doing this like converting the string to a datetime and then sorting?

[deanhystad]

What do you mean by "SQLite does not offer a Date type in the drop down"? What "drop down"? What sqlite tool am I missing out on?

There is not a datetime table type, so you have choices of saving date as a string, float or int. If a string I suggest using a format that is sortable: (YYYY-MM-DD).

If you made a bad choice on the date string format you can use a key when sorting. You could convert dates to a datetime object, or you could make a tuple(year, month, day), or you could covert to a date string amenable to sorting. This code converts the date string to a tuple(year, month, day) of ints.

dates = ["6-11-1992", "6-5-1992", "6-7-1962"]

def date_sort_key(date_str):
    m, d, y = map(int, date_str.split('-'))
    return (y, m, d)

dates.sort(key=date_sort_key)

print(dates)

Output:
['6-7-1962', '6-5-1992', '6-11-1992']

[hammer]

I realize now what you are saying about date format matters when sorting- yyyy-mm-dd will always be increasing in value for future dates where mm-dd-yyyy can be all over the place.
I think i will use above strategy with key to just sort returned list from query before displaying.

Thank you.

I use DBBrowser for SQLite and it has a dropdown when creating the field that provides options: Text,Integer,BLOB,Real,Numeric. Sorry for not presenting that point better.

[hammer]

How do you take a query return that has multiple fields and sort on the one date field and rearrange the rows returned accordingly?

[deanhystad]

DBBrowser is a nice tool. I don't do a lot of database work, but that will come in handy. Thanks!

I searched around a bit and I don't think you can ask the query to sort the results using a sorting key. That makes sense since the sorting key code is in your program, not in the database. You'll have to retrieve all the results and sort them in a list. Hope that is ok. I used DBBrowser to make a little database with my dates in it (table=date, month, day, year)

import sqlite3 as sql

def sort_by_date_str(x):
    m, d, y = map(int, x[0].split('-'))
    return (y, m, d)

connection = sql.connect("testdb.db")
cursor = connection.cursor()
results = cursor.execute("SELECT * FROM dates").fetchall()
print("Unsorted", results)

results.sort(key=sort_by_date_str)
print("Oldest->Newest", results)

results.sort(key=sort_by_date_str, reverse=True)
print("Newest->Oldest", results)

Output:
Unsorted [('6-5-1992', 6, 5, 1992), ('6-7-1962', 6, 7, 1962), ('6-11-1992', 6, 11, 1992)]
Oldest->Newest [('6-7-1962', 6, 7, 1962), ('6-5-1992', 6, 5, 1992), ('6-11-1992', 6, 11, 1992)]
Newest->Oldest [('6-11-1992', 6, 11, 1992), ('6-5-1992', 6, 5, 1992), ('6-7-1962', 6, 7, 1962)]

[hammer]

Thanks for the solution above. I am now studying up on sorting so I can understand how you knew to do that.

In your key method i think you are taking the first field in the returned row, declaring it is m,d,y format separated by '-', and you are returning it to the sort in y,m,d format (which sorts as expected).
Something I can not figure out is you are not passing your method anything and it requires "x".

Just out of curiosity, since I have used .strptime and understand it a little better, and if a datetime sorts correctly, could the following be substituted into your method as well?

def sort_by_date_str(x):
    return datetime.strptime(x[0],'%m-%d-%y')
   

[deanhystad]

It is assumed that the key function will be passed an item from the collection to be sorted. You can read about it here:

https://docs.python.org/3/howto/sorting.html

Datetime objects would work great for sorting, so a sorting key that created datetime objects from date strings would work great.

[hammer]

Thank you.
I tried both and either give same result.
Problem resolved.