mysql - 一列中按日期和时间查询过滤，另一列中按毫秒查询查询

我有下表：

Time_Stamp ,Time_Stamp_ms
'2014-04-11 13:33:24', '879', '5555', '4444', '3333', '2222', '1111', '123', '1', 'text'
'2014-04-11 13:33:24', '899', '5555', '4444', '3333', '2222', '1111', '123', '2', 'text'
'2014-04-11 13:33:24', '919', '5555', '4444', '3333', '2222', '1111', '123', '3', 'text'
'2014-04-11 13:33:24', '939', '5555', '4444', '3333', '2222', '1111', '123', '4', 'text'
'2014-04-11 13:33:24', '959', '5555', '4444', '3333', '2222', '1111', '123', '5', 'text'
'2014-04-11 13:33:24', '979', '5555', '4444', '3333', '2222', '1111', '123', '6', 'text'
'2014-04-11 13:33:24', '999', '5555', '4444', '3333', '2222', '1111', '123', '7', 'text'
'2014-04-11 13:33:25', '19', '5555', '4444', '3333', '2222', '1111', '123', '8', 'text'
'2014-04-11 13:33:25', '39', '5555', '4444', '3333', '2222', '1111', '123', '9', 'text'
'2014-04-11 13:33:25', '59', '5555', '4444', '3333', '2222', '1111', '123', '10', 'text'
'2014-04-11 13:33:25', '79', '5555', '4444', '3333', '2222', '1111', '123', '11', 'text'
'2014-04-11 13:33:25', '99', '5555', '4444', '3333', '2222', '1111', '123', '12', 'text'

如您所见，由于MySQL Server 5.5，我在单独的列中有一个Time_Stamp（日期时间）和一个Time_Stamp_ms（整数）。我的主键是Time_Stamp和Time_Stamp_ms。

我想过滤发生的一切
在2014-04-11 13：33：24.879和2014-04-11 13：33：25.79之间
并显示所有数据

我尝试了几种变体：

SELECT * FROM table WHERE (concat(Time_Stamp,'.',Time_Stamp_ms)
BETWEEN '2014-04-11 13:33:24.879' AND '2014-04-11 13:33:25.79');

也

SELECT *
FROM table
WHERE ( '2014-04-11 13:33:24' < Time_Stamp
OR ('2014-04-11 13:33:24' = Time_Stamp AND '879' <= Time_Stamp_ms))
AND ( '2014-04-11 13:33:25' < Time_Stamp
OR ('2014-04-11 13:33:25' = Time_Stamp AND Time_Stamp_ms < '79'))

最后但并非最不重要：

SELECT *
FROM table
WHERE ( '2014-04-11 13:33:24' < Time_Stamp
OR ('2014-04-11 13:33:24' = Time_Stamp AND 879 <= Time_Stamp_ms))
AND ( '2014-04-11 13:33:25' < Time_Stamp
OR ('2014-04-11 13:33:25' = Time_Stamp AND Time_Stamp_ms < 79))

这最后一个给我的结果没有意义（例如，它向我显示了2014-04-11 13:33:27的数据）

有人有什么想法吗？

最佳答案

所以可能只是因为您错误键入了运算符。

尝试这个：

SELECT *
FROM table
WHERE ( '2014-04-11 13:33:24' < Time_Stamp
OR ('2014-04-11 13:33:24' = Time_Stamp AND 879 <= Time_Stamp_ms))
AND ( '2014-04-11 13:33:25' > Time_Stamp //--> here
OR ('2014-04-11 13:33:25' = Time_Stamp AND Time_Stamp_ms < 79))

关于mysql - 一列中按日期和时间查询过滤，另一列中按毫秒查询查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23077415/