Time vs DateTime

Just curious.
Looking through nice yii3/yii-demo observe that table columns like ‘created_at’, ‘deleted_at’ and ‘updated_at’ are created using cycle…TimestampedMapper or SoftDeletedMapper and both default to using ‘DateTimeImmutable’ class/object and ‘datetime’ data type.

Yii2 docs advocate integer using time() for these columns, a practice I prefer over datetime, even though I generally work with small data sets with low transaction volume the integer just seems more efficient, but does lack the date range.

It’s nice to use defaults from a framework to get a quick start, but a migration may present an extra challenge if datamanager wishes to convert from integer to datetime.

Is the example just a suggested approach, or has there been a change in thinking for this and other Yii3 defaults?

Mike B

It’s just a way we decided to follow for a demo. Nothing more. Using int-s is still OK.

Just solved the same dilemma, prefer Datetime over int.
Solution is simple, don’t use of TimestampBehavior, set default values in MySQL this way:

CREATE TABLE `sample_table` (
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Hello @petr-panek

I agree your example works fine with MySql, but not with all other databases.

For db design, I might agree with constraints on type and range, but not with defaults that the ‘customer’ may change arbitrarily or through business requirement. Abstracting defaults into code (like Behaviours or Traits) enables flexibility and testing at application level, with less tinkering on db schema.

Just a POV

1 Like