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 or maybe you only know year or even decade. 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.

This is for storing old family photos – everything from modern digital cameras with full time/date stamp to scans of pre-1900 prints.