Created date (created_at) and Updated date (updated_at) are different in php and mysql

Question

I have custom table and attachment is a structure of the database. I Save data with

$post_data['created_date'] = now();
$model = Mage::getModel("rapnet/rapnet")->addData($post_data)->save();

Created date is now() and updated date is "CURRENT_TIMESTAMP". So why both are different on my localhost? How can we solve this to keep both same while creating a new record?

Answer 1

I had this issue early (it because your database set to one locale, but server to another).

So, here is a solution.

First: don't use now() function, use Mage::getModel('core/date')->timestamp(); in Magento.

Second: don't use CURRENT_TIMESTAMP as a default value for your magento database. Set both created_at and updated_at fields to DATETIME type.

Just use this upgrade script:

$installer = $this;
$table = $installer->getTable('model/entity'); // values from your config.xml here
$installer->startSetup();
$table->addColumn($table, 'created_at', [
    'type'     => Varien_Db_Ddl_Table::TYPE_DATETIME,
    'comment'  => 'created_at',
]);
$table->addColumn($table, 'updated_at', [
    'type'     => Varien_Db_Ddl_Table::TYPE_DATETIME,
    'comment'  => 'updated_at'
]);
$installer->endSetup();

or this (if you already have these columns):

$installer = $this;
$table = $installer->getTable('model/entity'); // values from your config.xml here
$installer->startSetup();
$table->modifyColumn($table, 'created_at',[
    'type'     => Varien_Db_Ddl_Table::TYPE_DATETIME,
    'comment'  => 'created_at',
]);
$table->modifyColumn($table, 'updated_at',[
    'type'     => Varien_Db_Ddl_Table::TYPE_DATETIME,
    'comment'  => 'updated_at'
]);
$installer->endSetup();

Third: in your model add function _beforeSave()(and you shouldn't set date manually anymore) and in that function make next logic: if it just created set created_at to current time using function above, and set updated_at to current time.

It looks like:

public function _beforeSave()
{
    if($this->isObjectNew()){
        $this->setCreatedAt(Mage::getModel('core/date')->timestamp());
    }
    $this->setUpdatedAt(Mage::getModel('core/date')->timestamp());
    return parent::_beforeSave();
}

Fourth: also it is good practice in magento to call your columns created_at and updated_at.

Hope it will be helpful for you :)

Answer 2

This is timezone issue, mysql save CURRENT_TIMESTAMP in UTC format while your application time() may be picking your server timezone. You need to keep timezone same for both column. For more see here