Timestamps in MySQL

I have a field of type TIMESTAMP in mySQL database, when I access that through the model, it just returns a string in the format YYYY-MM-DD hh:mm:ss - so when I try to format the date using Yii::app()->format->datetime(), it gives me an erroneous date unless I call strtotime() with the value of the model.

Just wondering if that is the correct way to do it or whether there is a better way to handle dates?

Your approach is correct, you have to reduce the MYsql timestamp into something that CDateFormatter can like.

If you want to try something of more stilish, you can create a behavuor that translate authomatically at afterFind/beforeSave (once there was a cookbook about it, but i cannot find).

I think you’re looking for this cookbook page on AR and timestamps.

If you’re using Yii 1.1, you can instead use CTimestampBehavior.

But all this is about writing timestamps in the DB, no about getting them in the right format. In fact, MySQL itself translates the timestamp, so the best solution would to read the attribute with (MySQL) “UNIX_TIMESTAMP(myattr) AS myattr”. But I don’t know if it can be done in Yii (changing the way an attribute is read from the DB), so, as zaccaria suggested, overloading afterFind() in your model would be a way out.

Thanks - I’ve added an afterFind function to convert the string back to a timestamp and everything worked. (I don’t need to worry about saving because that is automatically done by MySQL).