In patches table, the dates should be represented as dates, not strings

Feature request

What new feature do you want?

When performing a query like select * from patches order by installed_on the query sorts the column on M/D/YYYY STRING instead of a date. When attempting to use SQL to have it sort as a date it turns out that the string format is representing dates wihout leading zeros, making it impossible to sort it like this:

SELECT csname, 
installed_on, substr(installed_on,7,4) || substr(installed_on,1,2) || substr(installed_on,4,2) AS sort_date 
FROM patches ORDER BY sort_date ASC

As the substrings are moving targets without leading zeros.

How is this new feature useful?

If you are trying to create a report of patch levels for windows hosts, none of the patch dates come in order, so you have to post process the data instead of being able to use it right from osquery, or kolide/fleet.

How can this be implemented?

I looked at this PR: https://github.com/osquery/osquery/pull/2837#issuecomment-668347723
and https://github.com/osquery/osquery/blob/master/specs/windows/patches.table

As I understand it, osquery uses a subset of sqlite for virtual tables, and sqlite doesn't have date datatypes. It seems to me if the installed_on string were formatted as YYYYMMDD that would be sortable in an asc or desc fashion, or if the column were EPOCH time based, that would be sortable as well.

Thank you.

Start with specs/windows/patches.table and the reported SELECT query to understand how installed_on is exposed. Compare the proposed sortable date representations against SQLite's documented date handling and the existing patches table behavior. Done means installed_on can be ordered chronologically in ascending and descending queries without post-processing.