Date/time storage problem!!! Help


The problem is, how to store date/time in my tables. Should I store it as an INTEGER or as a DATE, TIME, DATETIME? This table design should be totally independend on database system. In other words, there shouldn’t be problem to migrate from for example MySQL to Oracle or SQLite (and don’t WASE my time to update php code for Yii!) What do you preffer or use? Show me some implementations! :P

I use DATE, it use the format YYYY-MM-DD, so is compatible with any other database that uses the same format.

If you want to be sure, use the function afterFind and beforeSave for convert from the localized format to the database one.

In this function you can use a global function for the actual conversion, this will create for you a unique point of change in the really unfortunate case that you will migrate to a database that not support the format YYYY-MM-DD.

Thanks for fast reply ;) So, actually, MySQL and SQLite stores the date in format YYYY-MM-DD so its compatible. I saw, when you’re connecting to Oracle DB, in config file, where you specify the database connection params, there is attribute for dateTime format (I dont know if this param is for Oracle, or it’s just a some variable used in framework by prgrammer), I didn’t test it yet so I don’t know.

Yes, afterFind and beforeSave is a solution but I’m lookin’ for some more elegant solution - dont write the code to all afterFind and beforeSave funcs. Someone wrote, there is a solution using behavioral. When I use the solution with afterFind… should I use dateFormatter inside? :) Could you be more speciffic with your third paragraph? :)

There is an extension that does the coversion between the I18n format and the database one, it uses the behavior that works on the afterFind and beforeSave.

Take a look here, you can either use this extension or copy the code you need from it.

Thanks for advice, I’ll try it! Seems to be good. Thanks zaccaria, hope there won’t be other problems because this date/time datatypes makes me crazy!