mysql强制转换为间隔 [英] mysql cast as interval

问题描述

是否可以在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 没有任何意义。

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强制转换为间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！