MySQL ColumnType::Time does does not populate model from setAttribute()

Question

I have a record with a time field set like this:

'startTime'         => array(AttributeType::DateTime, 'column'=>ColumnType::Time, 'required' => true),
'endTime'           => array(AttributeType::DateTime, 'column'=>ColumnType::Time),

The data is stored in the database as a string like this:

10:45:53

When I populate models from an elementCriteria query, the string value "10:45:53" does not work with creating a new DateTime object so I get false returned.

I would need to supply a full date in order to get a valid DateTime object back.

How should I deal with this? Is this i a bug in core? Why have ColumnType::Time if its not going to work correctly when pulling the value from the database?

Answer 1

It turns out that I don't think you should really use craft's DateTime object for time only. I suppose there is a case where you might want but if you just have a "time of day" absent a date, I ended up using AttributeType::String for them, but still use a ColumnType::Time for the database.

Then all I needed to do is override populateModel method to do a little magic to convert them from 24 hour format to 12 hour format like this:

public static function to12Hour($dbTime) {

    $dbTime = explode(":", $dbTime);

    if($dbTime[0] > 12) {
        $ampm = "PM";
        $dbTime[0] = $dbTime[0] - 12;
    } else if($dbTime[0] == 0) {
        $ampm = "AM";
        $dbTime[0] = 12;
    } else {
        $ampm = "AM";
    }

    return $dbTime[0] . ":" . $dbTime[1] . " " . $ampm;
}

public static function populateModel($values) {

    $model = parent::populateModel($values);

    if ($values['startTime']) {
        $model->setAttribute('startTime', self::to12Hour($values['startTime']));
    }

    if ($values['endTime']) {
        $model->setAttribute('endTime', self::to12Hour($values['endTime']));
    }

    return $model;
}

You just need to make sure you're storing the time in 24 hour format and to ensure that is the case, I set up my attribute types like this:

        'startTime'         => array(AttributeType::String, 'column'=>ColumnType::Time, 'required' => true, 'matchPattern' => '/^\d\d:\d\d:\d\d$/'),
        'endTime'           => array(AttributeType::String, 'column'=>ColumnType::Time, 'matchPattern' => '/^\d\d:\d\d:\d\d$/'),