0

How should I use MySQL's FROM UNIXTIME correctly in CDbMigration in Yii 1.x?

I have borrowed solution of converting current time given as timestamp, to MySQL's DateTime field from this answer and when just printing it:

echo 'FROM_UNIXTIME('.$base.')'."\n";
echo 'FROM_UNIXTIME('.$sixDaysLater.')'."\n";

everything seems fine:

FROM_UNIXTIME(1418223600)
FROM_UNIXTIME(1418742000)

But, when I'm trying to use the same technique as a part of my migration:

$this->insert('contents', array
(
    'author_id'=>1,
    'type'=>5,
    'status'=>1,
    'category'=>1,
    'title'=>'title',
    'body'=>'body',
    'creation_date'=>'FROM_UNIXTIME('.$base.')',
    'modification_date'=>'FROM_UNIXTIME('.$base.')',
    'availability_date'=>'FROM_UNIXTIME('.$sixDaysLater.')',
    'short'=>'short'
));

This fails -- that is, migration goes fine, but I can see in phpMyAdmin, that related fields for this record has been populated with zeros (0000-00-00 00:00:00), not with the expected value.

What am I missing? Is it, because values in insert are being encoded / escaped?

Community
  • 1
  • 1
trejder
  • 17,148
  • 27
  • 124
  • 216
  • 1
    all-zeroes dates means that you tried to insert an invalid date string. have you checked what the `$base` value is at the times you get those invalid dates? – Marc B Dec 10 '14 at 14:52
  • Yes, as you can see above (first example), the values are correct timestamp. Basing on answer below and my own second thought, I assume, that the problem is because entire value is escaped. – trejder Dec 11 '14 at 09:34

1 Answers1

2

You can use CDBExpression instead:

new CDbExpression("NOW()");

I mean:

'creation_date'=>new CDbExpression("NOW()")

Or if you want to use FROM UNIXTIME you can do the same.

CDbExpression represents a DB expression that does not need escaping.

Ali MasudianPour
  • 14,329
  • 3
  • 60
  • 62