Order_By custom date in peewee for SQLite

Question

I made a huge misstake building up a database, but it works perfectly except for 1 feature. Changing the program in all the places where it needs to be changed for that feature to work would be a titanic job of weeks, so let's hope this workaround is possible.

The issue: I've stored data in a SQLite database as "dd/mm/yyyy" TextField format instead of DateField.

The need: I need to sort by dates on a union query, to get the last number of records in that union following my custom date format. They are from different tables, so I can't just use rowid or stuff like that to get the last ones, I need to do it by date and I can't change the already stored data in the database because there are already invoices created with that format ("dd/mm/yyyy" is the default date format in my country).

This is the query that captures data:

records = []
limited_to = 25
union = (facturas | contado | albaranes | presupuestos)
    for record in (union
            .select_from(union.c.idunica, union.c.fecha, union.c.codigo,
                         union.c.tipo, union.c.clienterazonsocial,
                         union.c.totalimporte, union.c.pagada,
                         union.c.contabilizar, union.c.concepto1,
                         union.c.cantidad1, union.c.precio1,
                         union.c.observaciones)
            .order_by(union.c.fecha.desc())  # TODO this is what I need to change.
            .limit(limited_to)
            .tuples()):
        records.append(record)

Now to complicate things even more, the union is already created by a really complex where clause for each database before it's transformed into an union query.

So my only hope is: Is there a way to make order_by follow a custom date format instead?

To clarify, this is the simple transformation that I'd need the order_by clause to follow, because I assume SQLite wouldn't have issues sorting if this would be the date format:

def reverse_date(date: str) -> str:
    """Reverse the date order from dd/mm/yyyy dates into yyyy-mm-dd"""
    yyyy, mm, dd = date.split("/")
    return f"{yyyy}-{mm}-{dd}"

Note: I've left lot of code out because I think it's unnecesary. This is the minimum amount of code needed to understand the problem. Let me know if you need more data.

Update: Trying this workaround, it seems to work fine. Need more testing but it's promising. If someone ever faces the same issue, here you go:

.order_by(sqlfunc.Substr(union.c.fecha, 7)
          .concat('/')
          .concat(sqlfunc.Substr(union.c.fecha, 4, 2))
          .concat('/')
          .concat(sqlfunc.Substr(union.c.fecha, 1, 2))
          .desc())

Happy end of 2020 year!

Answer 1

As you pointed out, if you want the dates to sort properly, they need to be in yyyy-mm-dd format, which is the text format you should always use in SQLite (or something with the same year, month, day, order).

You might be able to do a rearrangement here using re.sub:

.order_by(re.sub(r'(\d{2})/(\d{2})/(\d{4})', r'\3-\2-\1',
                 union.c.fecha))

Here we are using regex to capture the year, month, and day components in separate capture groups. Then, we replace with these components in the correct order for sorting.