How do you add time or days to a current utc_timestamp?
I am using;
new CDbExpression('UTC_TIMESTAMP()')
for both 'created' and 'updated' fields in my mysql table but would like to add an 'expiry' field which would allow 4 days from creation date. I presume this is possible but am unsure how.
for insert/update current time
UPDATE table SET created = NOW()
for 4 days from creation date SELECT * FROM table WHERE created > DATE_SUB( NOW( ), INTERVAL 4 DAY )
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
In MySQL :
ALTER TABLE `table`
ADD expiry datetime DEFAULT DATE_ADD( utc_timestamp( ) , INTERVAL 4 DAY);
"The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE."
So, this is explicitely documented limitation
you have to create TRIGGER if your MySQL Version < 5.6.5
BUT
As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table.
Reference :