The Ultimate Business Machine - Archives
List of Categories : Commentary * Database * Singapore * Technology * Travel *
Sat 06 Aug 2011
Handling NULLs in SQL and NSDate Fields
Category : Technology/NULLDates.txt
I've finally managed to solve a long-standing design problem, probably because I'm settled now with Lion and have more time to think.
The problem I was having is with NULLs in date fields. Why do we have NULLs? When we don't know what an actual date will be when we're filling up a form earlier in time - e.g., an insurance claims data entry screen, when we don't know when a surveyor will be able to inspect a vehicle damaged in an accident.
Both PostgreSQL and MySQL will accept NULLs in date fields. But the problem comes when we try to show these dates in a Cocoa Mac OS X (or iOS) screen, using fields that have been formatted as dates. This is because NSDateFormatter requires the data entry to be a properly formatted date value, and NULLs are not.
The problem is compounded when we're working with tabular data and, to retain our sanity, we want all data in a single column to belong to the same data type, so mixing NULLs and proper date values won't do.
I've since worked out a scheme whereby NULL dates from the database get converted to [NSDate distantDate], and when I see as [NSDate distantDate]'s coming back to the database, I convert them to NULLs.
So, inside Cocoa (and iOS), my tabular data will work consistently. Each column is a distinct, consistent data type.
But the problem with showing [NSDate distantDate]'s as actual data values in the user interface is that it clutters up the data in places where the user would have expected blanks in the dates, like below:
So, I've always wanted NULL dates to show up more naturally, like in the screen shot below, and I've finally solved the problem by writing a custom sub-class for NSDateFormatter. Turns out to be not too hard. I wonder now why I took so long. Probably because it's hard to see clearly when so many things are moving all around, with the migration to Lion.
Liya version 2.0 for Lion is now ready for download.