| The Ultimate Business Machine Technology, business And, not least, about by: Bernard Teo
|
The Ultimate Business Machine - ArchivesList of Categories : Database * Technology * Commentary * Singapore * Travel * Sat 06 Aug 2011 Handling NULLs in SQL and NSDate FieldsCategory : 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:
[NSDate distantDate] should be 4001-01-01 00:00:00 +0000, but it is 4000-12-31 19:00:00 -0500 in the screen shot above because that is [NSDate distantDate] in Montreal. I set my location to Montreal, or it could be any other city, to make sure my Time Stamp fields continue to work correctly whichever country or locale my Mac happens to be in. This is something I've spent a lot of care on, that my database frameworks and apps like Liya work as fluidly with shifting locales, as Apple's Macs, iPhones and iPads do. 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. Somehow I just couldn't make a version work for Snow Leopard anymore - Xcode complaining about some problem with dynamic libraries from 10.6. So, going forward, I'm now going to work only with this Lion version and drop the Snow Leopard version. I've also submitted this version to the Mac App Store, just to see what the submission process is like. It seems a bit easier than with iOS apps. This is my first Mac App Store app and it's free.
|
Mac@Work
Services running on this server, a Mac Mini running Mac OS X 10.9.2 Mavericks:
all set up using MailServe, WebMon, DNS Enabler, DNS Agent, VPN Enabler, Liya and our SQL installers, all on Mavericks. |
||||||||||||||||||||||||||
