A Different (Better?) Way To Deal With Date And Datetime

I have long been fed up with the way date and time attributes in models have to be handled on a case-by-case basis with no real help from the framework - so I came up with a CBehavior that enables automatic conversion between DATE/DATETIME SQL strings and (UNIX) integer timestamps.

Source code here.

Example of a model that uses the behavior:




/**

 * @property int $birthday

 */

class Foo extends CFormModel

{

    public $birthday_date = '1975-07-07';


    public function behaviors()

    {

        return array(

            'datetime_accessors' => array(

                'class' => 'GDateTimeAccessors',

            ),

        );

    }

}



Note the @property-annotation, which documents the synchronous accessor for the DATE string attribute.

Here’s an example of how to use this:




        $test = new Foo();


        echo $test->birthday_date."\n";

        echo 'timestamp accessor: '.$test->birthday."\n";


        $test->birthday = time();


        echo 'new date: '.$test->birthday_date."\n";

        echo 'timestamp accessor: '.$test->birthday."\n";


        try {

            $test->birthday = 'ouch';

        } catch (CException $e) {

            echo 'expected write-error: '.$e->getMessage()."\n";

        }


        $test->birthday_date = 'bad bad';


        try {

            $time = $test->birthday;

        } catch (CException $e) {

            echo 'expected read-error: '.$e->getMessage()."\n";

        }



And the output from running the above example:




1975-07-07

timestamp accessor: 173937600

new date: 2013-01-18

timestamp accessor: 1358485200

expected write-error: property Foo::$birthday accepts only integer or null-values

expected read-error: property Foo::$birthday_date contains an invalid value



By convention, I name my DATE and DATETIME columns in the database with a "_date" or "_datetime" suffix, so that I can manually implement synchronous get/set-accessors for integer timestamp values - this behavior does the same thing, but dynamically for any attributes following the naming conventions.

This is brand new and barely tested, beyond the example shown above, so let me know if this works for you or not.

This is working well for me - with a couple more additions.

First off, having to configure the CJuiDatePicker on a case by case basis is error-prone and clunky - so I extended CActiveForm with a datePicker() method:




class ActiveForm extends CActiveForm

{

    /**

     * Configure and render a CJuiDatePicker.

     * Date format is configured for compatibility with MySQL date-format.

     *

     * @see GDateTimeAccessors

     *

     * @param CModel $model

     * @param string $attribute

     * @param array  $widgetOptions

     *

     * @return string

     */

    public function datePicker(CModel $model, $attribute, $widgetOptions = array())

    {

        $name = CHtml::activeName($model, $attribute);


        $id = Html::getIdByName($name);


        $junk_name = CHtml::ID_PREFIX . CHtml::$count++;


        $options = CMap::mergeArray(

            array(

                'name'        => $junk_name,

                'options'     => array(

                    'showAnim'   => 'fold',

                    'dateFormat' => 'mm/dd/yy', // American date format

                    'altFormat' => 'yy-mm-dd', // MySQL date format

                    'altField' => '#'.$id,

                ),

            ),

            $widgetOptions

        );


        return

            Html::activeHiddenField($model, $attribute) .

            $this->controller->widget('zii.widgets.jui.CJuiDatePicker', $options, true);

    }

}



I have this method set up to create and configure an alternative field - so that the date displays in American date-format to the user, but submits in MySQL DATE-compatible format.

Unfortunately, it turns out the jQuery DatePicker only supports writing to the altField - it does not initialize from the altField correctly. Rather than submitting a feature request (which could take months) I created this constructor patch:




/**

 * Constructor patch for jQuery UI DatePicker

 * Automatically initializes the input with the value from the altField

 */

(function($) {

    var _datepicker = jQuery.fn.datepicker;


    $.fn.datepicker = function(options) {

        var $date = _datepicker.apply(this, arguments);


        if (options.altFormat && options.altField) {

            var altValue = $(options.altField).val();

            var value = $.datepicker.parseDate(options.altFormat, altValue);

            var dateFormat = _datepicker.call(this, 'option', 'dateFormat');

            $(this).val($.datepicker.formatDate(dateFormat, value));

        }

    };

})(jQuery);



Make sure you include that after jQuery UI, so it can patch up the datepicker() constructor correctly.

Using a date-picker on a form is now finally as simple as it ought to be:


<?php echo $form->datePicker($model, 'birthday_date'); ?>

Very nicely done and though out. What data type are you using to store your field in the DB?

[color="#006400"]NOTE: moved to proper section (Tips, Snippets and Tutorials instead of General Discussion for Yii 1.1.x)[/color]

I designed for use with DATE and DATETIME, but I guess there’s no reason this wouldn’t work with a VARCHAR, if (for some odd reason) that’s what you wanted.

Some thoughts about the behavior:

First, why do you prefer unix timestamps over DateTime instances?

Formats seem to be no problem, as MySQL always returns a standardized representation of date and datetime fields. I’m not sure if this is also true for other DBMS though. Localized representations could have caused trouble.

One thing that might be problematic with the current implementation are timezones. The dates stored in DB must match php’s timezone, right? Think this should at least be noted somewhere.

About the behavior settings: Why are you using static ones? This prevents the behavior from being used with several models that require different configuration.

Another thought about configuration: Mapping properties to other properties using postfixes is a convenient convention. But the same mapping could be achieved using an associative array, which would give you even greater flexibility. Maybe there is a way to combine both approaches.

I’m very comfortable with timestamps, but you should be able to change this quite easily, if you prefer DateTime.

That’s outside the scope of this behavior - you need to deal with timezone settings yourself, same as any other normal day with PHP (and Yii.)

I guess that could cause problems with modules, yeah. Ideally, each module should have it’s own configuration, I suppose. Seems unlikely you would use multiple date-formats in the same application? So for the time being, I kept them static, to avoid having to configure them individually, since I want my application to use a consistent date-format.

I’m not sure what you mean? I wanted this to work with CHtml and CActiveForm, which works with attributes, not array indices.

I have no plans to build and maintain a module or extension - just sharing code here.

Ah, okay. Think it’s good enough for a show case. No need to prepare for all eventualities then. Maybe it’s even better to concentrate on the idea instead of cluttering it with lots of configuration possibilities. :)

Posted a very small update to the gist - there was no reason to throw when the string-property contains an invalid value that can’t be converted to a timestamp; instead it now returns null. This means you can add a ‘required’ validator for the timestamp-property, if you like.

Having the string-property populated with an invalid string is expected behavior when you’re receiving input from a form, so throwing didn’t actually make any sense - but keep in mind that any computations (inside or outside the model class) based on the timestamp-value now needs to be prepared to handle the possible null-value.