Business Machine

Technology, business
and innovation.

And, not least, about
the Mac.

Weblog Archive Cutedge

by: Bernard Teo

Creative Commons License

Copyright © 2003-2012
Bernard Teo
Some Rights Reserved.

The Ultimate Business Machine - Archives

List of Categories : Database * Technology * Commentary * Singapore * 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:

[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.

Posted at 10:31AM UTC | permalink

Put your Mac to Work Now how would you do something like that?

Weblogs. Download and start a weblog of your own.

A Mac Business Toolbox
A survey of the possibilities

A Business Scenario
How we could use Macs in businesses

VPN Enabler for Mavericks

MailServe for Mavericks

DNS Enabler for Mavericks

DNS Agent for Mavericks

WebMon for Mavericks

Luca for Mavericks

Liya for Mountain Lion & Mavericks

Postfix Enabler for Tiger and Panther

Sendmail Enabler for Jaguar

Services running on this server, a Mac Mini running Mac OS X 10.9.2 Mavericks:

  • Apache 2 Web Server
  • Postfix Mail Server
  • Dovecot IMAP Server
  • Fetchmail
  • SpamBayes Spam Filter
  • Procmail
  • BIND DNS Server
  • DNS Agent
  • WebDAV Server
  • VPN Server
  • PHP-based weblog
  • MySQL database
  • PostgreSQL database

all set up using MailServe, WebMon, DNS Enabler, DNS Agent, VPN Enabler, Liya and our SQL installers, all on Mavericks.