mysql 时间戳 小时,MySQL – 来自列的Unix时间戳,包括小时,天,月

我有MySQL表与列(日,月,小时).是否可以在查询本身内构建unix时间戳？

我正在寻找这样的东西：

SELECT unix(2016, a.month, a.day, a.hour, 00, 00) as d

FROM a

WHERE d > 456456 AND d < 789789

值456456和789789由应用程序提供.我知道,我可以从STR_TO_DATE创建unix时间戳,但是我必须为WHERE子句中的每个比较创建它,我恐怕会很慢.

创建表格：

CREATE TABLE `a` (

`data_a` smallint(6) NOT NULL DEFAULT '0',

`hour` tinyint(4) NOT NULL,

`day` tinyint(4) NOT NULL,

`month` tinyint(4) NOT NULL,

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

MySQL版本5.0

解决方法:

而不是转换WHERE子句中的列,而是使用应用程序给出的常量.这些计算只需要进行一次.

我们可以用简短的形式写出这个条件：

SELECT STR_TO_DATE(...)

FROM a

WHERE (2016, a.month, a.day, a.hour, 0, 0)

>

( YEAR(FROM_UNIXTIME(456456)),

MONTH(FROM_UNIXTIME(456456)),

DAY(FROM_UNIXTIME(456456)),

HOUR(FROM_UNIXTIME(456456)),

MINUTE(FROM_UNIXTIME(456456)),

SECOND(FROM_UNIXTIME(456456))

)

AND (2016, a.month, a.day, a.hour, 0, 0)

<

( YEAR(FROM_UNIXTIME(789789)),

MONTH(FROM_UNIXTIME(789789)),

DAY(FROM_UNIXTIME(789789)),

HOUR(FROM_UNIXTIME(789789)),

MINUTE(FROM_UNIXTIME(789789)),

SECOND(FROM_UNIXTIME(789789))

)

或者是在5.7之前的版本中,有更多机会有效使用索引的长形式：

SELECT STR_TO_DATE(...)

FROM a

WHERE ((2016 > YEAR(FROM_UNIXTIME(456456)))

OR (2016 = YEAR(FROM_UNIXTIME(456456))

AND a.month > MONTH(FROM_UNIXTIME(456456)))

OR (2016 = YEAR(FROM_UNIXTIME(456456))

AND a.month = MONTH(FROM_UNIXTIME(456456))

AND a.day > DAY(FROM_UNIXTIME(456456)))

OR (2016 = YEAR(FROM_UNIXTIME(456456))

AND a.month = MONTH(FROM_UNIXTIME(456456))

AND a.day = DAY(FROM_UNIXTIME(456456))

AND a.hour > HOUR(FROM_UNIXTIME(456456))))

AND ((2016 < YEAR(FROM_UNIXTIME(789789)))

OR (2016 = YEAR(FROM_UNIXTIME(789789))

AND a.month < MONTH(FROM_UNIXTIME(789789)))

OR (2016 = YEAR(FROM_UNIXTIME(789789))

AND a.month = MONTH(FROM_UNIXTIME(789789))

AND a.day < DAY(FROM_UNIXTIME(789789)))

OR (2016 = YEAR(FROM_UNIXTIME(789789))

AND a.month = MONTH(FROM_UNIXTIME(789789))

AND a.day = DAY(FROM_UNIXTIME(789789))

AND a.hour < HOUR(FROM_UNIXTIME(789789)))

OR (2016 = YEAR(FROM_UNIXTIME(789789))

AND a.month = MONTH(FROM_UNIXTIME(789789))

AND a.day = DAY(FROM_UNIXTIME(789789))

AND a.hour = HOUR(FROM_UNIXTIME(789789))

AND 0 < MINUTE(FROM_UNIXTIME(789789))+SECOND(FROM_UNIXTIME(789789))))

这也将允许MySQL在此查询上使用索引(如果可用).如果尚未完成,则应创建索引(月,日,小时)(in this order!).

标签：mysql,timestamp,mysql-5-0

来源： https://codeday.me/bug/20190806/1604722.html