问题描述
是否可以在MySQL中将其强制转换为时间间隔?
is it possible to cast as an interval in MySQL?
我想将查询中的值作为时间间隔进行评估,例如:
I want to evaluate a value from the query as an interval, like this:
select DATE_SUB(NOW(),CAST(user.expiry_interval AS interval))
其中user.expiry_interval为间隔1周或类似的
where user.expiry_interval is 'INTERVAL 1 WEEK' or something like that
select DATE_SUB(NOW(),CAST('INTERVAL 1 week' AS INTERVAL))
推荐答案
,而是,它将人类可读的持续时间字符串转换为整数。因此,广播到 INTERVAL
没有任何意义。
INTERVAL
is not a data type, but a keyword that converts a human-readable "duration" string into an integer. As such, "casting to INTERVAL
" makes no sense.
如果您的 interval输入变量,则这可以工作,但是您不能将整个字符串从字符串中提取出来,而只能将字符串 SELECT * FROM tbl
投射到查询表达式中。您可以为数字操作数使用变量(如下所示),但我想就是这样。
If your "interval" input is variable, then this can work but you can't take the entire thing from a string any more than you can "cast" the string "SELECT * FROM tbl"
into a query expression. You can use a variable for the numeric operand (see below) but I think that's it.
SELECT DATE_SUB(NOW(), INTERVAL `x` DAY) FROM `tbl`;
您必须求助于 Case
如果您想要比这更强大的逻辑。或者...首先将整数持续时间存储。
You'll have to resort to CASE
if you want logic that's stronger than that. Or... store integer durations in the first place.
这篇关于mysql强制转换为间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!