mysql - 按一列中的日期和时间以及另一列中的毫秒进行查询过滤

Question

我有下表:

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 的数据)

有人有什么想法吗？

Answer 1

所以可能只是因为您错误地输入了运算符..

试试这个:

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))