DB DateTime handling definition in Schema

[aliechti]

I'm struggling every time with the DateTime handling in queries and ActiveRecords. The problem is, that the Schema does not handle any DateTime conversion, it could be allot easier if it would.

In a form the DateTime value could have any format and the Model::load() method does just insert it with this format. It is possible to do a format check with a rule, but that does not solve this problem.

I've tested some possible solutions:

• Use a DateTime-Trait and override the __set() and __get() methods to do a conversion to a custom DateTime object which handles __toString() to convert it in the DB related format.
• Handle it with the before save events, for the DB conversion. This could be done in a Behavior.

The problem with this solutions is, that if the DB changes it could be possible that it accepts only another format. There are many other problems, because both solutions change the original value to something else. So my thought was to use the yii\db\Schema for the conversion. So it would be possible to convert it to the expected DB format and do not touch the model.

Another thought was to convert any datetime-field to a DateTime object. This would overcome a problem which would occur, if a value is handled on the client side like this MM-yyyy, which does not contain a day and could not be converted without this specific description. It would be handy if it would convert the date with the rule definition, so both sides are handled.

[samdark]

You mean PHP's DateTime objects?

[aliechti]

Yes

[cebe]

In a form the DateTime value could have any format and the Model::load() method does just insert it with this format. It is possible to do a format check with a rule, but that does not solve this problem.

why can't you use DateValidator and the timestampAttribute + timestampAttributeFormat? http://www.yiiframework.com/doc-2.0/yii-validators-datevalidator.html#$timestampAttributeFormat-detail

[aliechti]

Because MSSQL does not "really" support timestamp and if you have a field for a birthday-date, it isn't the right type for that.

[samdark]

MySQL supports int (which UNIX timestamp is) well.

[aliechti]

There are situations which need a date before 1970 or after 2038.

[cebe]

you can use a bigint on 64bit systems if that helps.

[cebe]

note that negative integers will work, so the range is about 1912 to 2038.

[samdark]

Overall I'm not saying it's not usable to have an ability to use DateTime like that, just point to existence of a workaround.

[aliechti]

If you have an environment which already uses datetime-fields it is just not practicable to change all fields to unix timestamp. By the way I've pointed out some workarounds which we already use. I just wanted to explain some possible solutions, which would not be that hacky.

I've seen that the version 2.0.4 has the method yii\db\Schema::insert(), which would make it easy. It would just need a:

foreach ($columns as $key => $column) {
    if ($column instanceof \DateTime) {
        $columns[$key] = $column->format('dbspecific-format');
    }
}

But that works only for the insert-statement, because the other ones do not have such a method in yii\db\Schema. Maybe it would make sense to implement a prepareColumns() method in yii\db\Schema, to do some DB specific conversions.

[aliechti]

@cebe @samdark So you would both agree that the current statement of using Yii2 is to just don't use DateTime DB Fields and use instead unix timestamps?

[nineinchnick]

I wouldn't recommend using unix timestamps in the db if there is native support for normal datetimes. In PHP I just use them as strings.

It would be nice if DateTime objects would be supported in Schema classes. Some formatters already handle them.

[aliechti]

This #9994 change does just handle insert and update statements.

It would also be nice to have the same feature in the condition statements. Maybe also to have the possibility to return automatically a DateTime object for a column, but that should be optional.

[samdark]

yiisoft/yii2#9994

[Tigrov]

• Supports PHPs DateTime instances db#725