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: ![](../Images/LiyaMessy.png)
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. ![](../Images/LiyaClean.png)
Liya version 2.0 for Lion is now ready for download.
Posted at 10:31AM UTC | permalink
|