数据库:Mysql
时间字段类型:BIGINT
期望SQL:
查询出特定时间段内的所有数据
比如: 2008-5-1 00:00:00 到 2009-10-1 00:00:00 内 11点 到 21点 之间的所有数据。
那位高手能解决问题,高分答谢!!!!!!!!!!!!!
时间字段类型:BIGINT
期望SQL:
查询出特定时间段内的所有数据
比如: 2008-5-1 00:00:00 到 2009-10-1 00:00:00 内 11点 到 21点 之间的所有数据。
那位高手能解决问题,高分答谢!!!!!!!!!!!!!
from yourTable
where convert(时间字段 , datetime) between '2009-05-01 00:00:00' and '2009-10-01 00:00:00'
and hour(convert(时间字段 , datetime) ) between 11 and 21
| seq | in_db_t | op_t | s_mac |
+-------------+---------------+---------------+-----------------+
| 90000001373 | NULL | NULL | NULL |
| 90000001359 | 1241782809674 | 1242486180000 | 118301358431488 |
| 90000001360 | 1241782847787 | 1242486180000 | 118301358431488 |
| 90000001357 | 1241782808884 | 1242486180000 | 118301358431488 |
| 90000001362 | 1241782852536 | 1242486180000 | 118301358431488 |
| 90000001361 | 1241782851867 | 1242486180000 | 118301358431488 |
| 90000001363 | 1241782860706 | 1242486180000 | 118301358431488 |
| 90000001356 | 1241782808693 | 1242486180000 | 118301358431488 |
| 90000001358 | 1241782809572 | 1242486180000 | 118301358431488 |
| 90000001355 | 1241782808056 | 1242486180000 | 118301358431488 |
| 90000001354 | 1241782807937 | 1242486180000 | 118301358431488 |
| 90000001353 | 1241782807195 | 1242486180000 | 118301358431488 |
| 90000001349 | 1241783649143 | 1242486180000 | 118301358431488 |
| 90000001351 | 1241783650017 | 1242486180000 | 118301358431488 |
| 90000001350 | 1241783649911 | 1242486180000 | 118301358431488 |
| 90000001348 | 1241783648975 | 1242486180000 | 118301358431488 |
| 90000001337 | 1238590280617 | 1242486180000 | 118301358431488 |
| 90000001338 | 1238590389675 | 1242486180000 | 118301358431488 |
| 90000001339 | 1238590389904 | 1242486180000 | 118301358431488 |
| 90000001346 | 1241783647969 | 1242486180000 | 118301358431488 |
| 90000001347 | 1241783648128 | 1242486180000 | 118301358431488 |
| 90000001344 | 1241783646674 | 1242486180000 | 118301358431488 |
| 90000001352 | 1241782806934 | 1242486180000 | 118301358431488 |
| 90000001341 | 1238590391520 | 1242486180000 | 118301358431488 |
| 90000001342 | 1238590392744 | 1242486180000 | 118301358431488 |
| 90000001324 | 1238589659411 | 1242486180000 | 118301358431488 |
| 90000001345 | 1241783646899 | 1242486180000 | 118301358431488 |
| 90000001330 | 1238590106447 | 1242486180000 | 118301358431488 |
| 90000001325 | 1238589660365 | 1242486180000 | 118301358431488 |
| 90000001329 | 1238590104953 | 1242486180000 | 118301358431488 |
| 90000001332 | 1238590107441 | 1242486180000 | 118301358431488 |
| 90000001333 | 1238590107549 | 1242486180000 | 118301358431488 |
| 90000001327 | 1238589683811 | 1242486180000 | 118301358431488 |
| 90000001328 | 1238590104768 | 1242486180000 | 118301358431488 |
| 90000001340 | 1238590391440 | 1242486180000 | 118301358431488 |
| 90000001343 | 1238590392880 | 1242486180000 | 118301358431488 |
| 90000001326 | 1238589587973 | 1242486180000 | 118301358431488 |
| 90000001334 | 1238590108361 | 1242486180000 | 118301358431488 |
| 90000001335 | 1238590108427 | 1242486180000 | 118301358431488 |
| 90000001336 | 1238590176087 | 1242486180000 | 118301358431488 |
| 90000001321 | 1238589640356 | 1242486180000 | 118301358431488 |
| 90000001322 | 1238589645376 | 1242486180000 | 118301358431488 |
| 90000001323 | 1238589646328 | 1242486180000 | 118301358431488 |
| 90000001331 | 1238590106525 | 1242486180000 | 118301358431488 |
| 90000001320 | 1238589634436 | 1242486180000 | 118301358431488 |
+-------------+---------------+---------------+-----------------+
其中 op_t 是时间条件 类型是 BIGINT
| 90000001360 | 1241782847787 | 1242486180000 | 118301358431488 |
| 90000001357 | 1241782808884 | 1242486180000 | 118301358431488 |
| 90000001362 | 1241782852536 | 1242486180000 | 118301358431488 |
| 90000001361 | 1241782851867 | 1242486180000 | 118301358431488 |
| 90000001363 | 1241782860706 | 1242486180000 | 118301358431488 |
比如 要求 2008-5-1 00:00:00 到 2009-10-1 00:00:00 内 11点 到 21点 之间的所有数据。 注意:条件是 op_t 数据入库的时候 op_t 是有 时间的 getTime()的 装换得到的
from yourTable
where FROM_UNIXTIME(op_t/1000) between '2009-05-01 00:00:00' and '2009-10-01 00:00:00'
and hour(FROM_UNIXTIME(op_t/1000)) between 11 and 21