Storing a NULL Value in a MySQL Date/Time Field

I had a table with a column of type 'time.' It's set up so that the values can be null, and the default is, well, NULL. The create code looks like this:


CREATE TABLE IF NOT EXISTS `tbl_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `subheading` varchar(255) DEFAULT NULL,
  `external_link` varchar(255) DEFAULT NULL,
  `link_text` varchar(255) DEFAULT NULL,
  `description` text,
  `is_featured` tinyint(1) DEFAULT NULL,
  `image_id` int(11) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `event_type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

Pretty straighforward, right? Wrong! It turns out that MySQL is pretty persnickety about what you put in a time column. If it considers it "illegal," it will default to its version of an "empty" field which, in this case, is "00:00:00" for zero hours, zero minutes, and zero seconds. Unfortunately, this is not really nothing, it's midnight!

So, why wasn't it just defaulting to NULL, like I'd asked it to? Although midnight as a translation for an illegal insert is arguably a pretty poor choice, the problem wasn't entirely MySQL's fault. When I tried to directly insert NULL without going through Yii (okay - I cheated and checked the "null" box in PHPMyAdmin just to see if it would take the value), it worked. In other words, you can use a NULL value in there if you ask it nicely. Knowing that, I tried a variety of different ways in my Yii code, like setting the attribute to 'NULL' in single quotes ('NULL'), null with no quotes (null), and plain old empty string (''). None of these things worked. MySQL considered all of them to be illegal entries.

What, you may ask, is the right way to ask MySQL to insert NULL in the time field, particularly if you're depending on Yii to do the asking for you? The answer is to use Yii's CDbExpression. When you have a time-related field, and you want a NULL value to be inserted, do it like this:

$model->myTimeColumn = new CDbExpression('NULL')

It's that simple. True, it takes a little extra code to make sure that's what you want, actually do the setting, etc., But it's much better than inadvertently setting it to midnight!