As an undergraduate, I was given a Bash assignment and one of the sub-tasks was to sort some .csv file by date. It took me two weeks to realise that the way we, as British humans, format the date (dd-mm-yyyy) is utterly useless to sort with and simply reversing the date (to yyyy-mm-dd) and treating it as a numerical type (example, 20150810) would guarantee an easy way to sort. It later transpired that formatting dates to yyyy-mm-dd is an ISO standard and what you should be doing when working with dates on computers and whatnot. At the time, though, my undergraduate mind was overjoyed that I managed to solve what was a tricky problem.
Given that previous lesson, you really would have thought that an experienced Joe, having already world exclusively solved the date sorting problem for the standards committees, would not be naive enough to use dates that follow dd-mm-yyyy format when programming. Well…
You see, I’m working with C# which is a pretty powerful programming language. It has fantastic Date and Time libraries that make working with the tricky task of dates and times a simpler process. You want a date with that format? “Sure, no problem.” says the C# library. “Oh, you want to convert it back? No worries!” Unfortunately, I was also using SQLite to store much of the user data. That’s not ‘unfortunately’ in the sense SQLite is awful, as it’s great. It’s just there is not much notion of a data type (by design), and certainly no notion of a Date or Time. It does have Text, however, and that’s pretty much it.
The data is being displayed in a DataGridView that’s populated by the SQLite database, which provides automatic sorting when you click on a column header. It’s a pretty useful, and it means that I don’t have to roll my own. Or so I thought.
Time goes by and there isn’t much of an issue; I felt like I had tested it thoroughly and just carried on. New items were being placed in the SQLite database in the correct order; it’s just if the user wanted to sort rows by date… Well, you can see where this is going. The DataGridView didn’t recognise my dates as “dates”, and sorted them based on their text value (because to SQLite, it is just text). 30-06-2015 is greater than 01-07-2015? I think not, but that’s the hole I dug myself. A simple job of sorting by date in ascending or descending order was now a ridiculous task of having to pull rows from the database, split the date and converting it to yyyy-mm-dd format, sorting it, then outputting it. I pat myself on the back for that awesome solution to a problem that never should have existed in the first place.
Thankfully, changing the way dates were input into the database was simple enough and now we can sort by using inbuilt libraries, or by asc and desc clauses in SQL, that are considerably faster. The moral is, use yyyy-mm-dd when you know you’re going to want to sort by date.
About the author
Joseph Williams is a University Instructor within the section of Computing, Digital Forensics and Cybersecurity.