How do you add time or days to a current utc_timestamp?
如何为当前的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.
对于我的mysql表中的'created'和'updated'字段,但是想添加一个'expiry'字段,该字段允许从创建日期起4天。我认为这是可能的,但我不确定如何。
3 个解决方案
#1
for insert/update current time
用于插入/更新当前时间
UPDATE table SET created = NOW()
UPDATE表SET创建= NOW()
for 4 days from creation date SELECT * FROM table WHERE created > DATE_SUB( NOW( ), INTERVAL 4 DAY )
自创建日期起4天内SELECT * FROM表创建的WHERE> DATE_SUB(NOW(),INTERVAL 4 DAY)
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
#2
In MySQL :
在MySQL中:
ALTER TABLE `table`
ADD expiry datetime DEFAULT DATE_ADD( utc_timestamp( ) , INTERVAL 4 DAY);
#3
"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."
“数据类型规范中的DEFAULT值子句指示列的默认值。除了一个例外,默认值必须是常量;它不能是函数或表达式。这意味着,例如,您无法设置date列的默认值是函数的值,例如NOW()或CURRENT_DATE。“
So, this is explicitely documented limitation
因此,这是明确记录的限制
you have to create TRIGGER if your MySQL Version < 5.6.5
如果MySQL版本<5.6.5,则必须创建TRIGGER
BUT
MySQL 5.6.5 changelog stats
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.
从MySQL 5.6.5开始,TIMESTAMP和DATETIME列可以自动初始化并更新为当前日期和时间(即当前时间戳)。在5.6.5之前,这仅适用于TIMESTAMP,并且每个表最多只有一个TIMESTAMP列。
Reference :
http://bugs.mysql.com/bug.php?id=27645
http://optimize-this.blogspot.in/
#1
for insert/update current time
用于插入/更新当前时间
UPDATE table SET created = NOW()
UPDATE表SET创建= NOW()
for 4 days from creation date SELECT * FROM table WHERE created > DATE_SUB( NOW( ), INTERVAL 4 DAY )
自创建日期起4天内SELECT * FROM表创建的WHERE> DATE_SUB(NOW(),INTERVAL 4 DAY)
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
#2
In MySQL :
在MySQL中:
ALTER TABLE `table`
ADD expiry datetime DEFAULT DATE_ADD( utc_timestamp( ) , INTERVAL 4 DAY);
#3
"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."
“数据类型规范中的DEFAULT值子句指示列的默认值。除了一个例外,默认值必须是常量;它不能是函数或表达式。这意味着,例如,您无法设置date列的默认值是函数的值,例如NOW()或CURRENT_DATE。“
So, this is explicitely documented limitation
因此,这是明确记录的限制
you have to create TRIGGER if your MySQL Version < 5.6.5
如果MySQL版本<5.6.5,则必须创建TRIGGER
BUT
MySQL 5.6.5 changelog stats
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.
从MySQL 5.6.5开始,TIMESTAMP和DATETIME列可以自动初始化并更新为当前日期和时间(即当前时间戳)。在5.6.5之前,这仅适用于TIMESTAMP,并且每个表最多只有一个TIMESTAMP列。
Reference :