Sorting a string before a number with Postgres and Django
I want to use fuzzy dates for sorting a Django query, so you can use a full date if you have it, but maybe you only know year and month. For example, Jan XX 2024 would come before Jan 1 2024 and Jan 2 2024. I was originally planning to store as a string 202401XX, 20240101, etc, but Postgres naturally sorts characters after numbers so this would put the date with no day AFTER the date with day specified.
I was thinking of converting the 20240101 to letters before saving to the database (X=A, 0=F, 1=G, etc) since this is a computationally cheap thing and I could control where everything sorted. Is this a terrible idea? Is there a better way? I don't want to do a custom Postgres query because that would be complicated with Django's built-in pagination
Ok, I'm an idiot. I was thinking I had to add a character to replace the unknown values but if I just use 202401 for January 2024 and 20240101 for January 1st 2024 it sorts fine. If I don't know the day I certainly won't know the time. Anyway, thanks to all who looked at this and those who tried to help.