How to correctly set time in sql query?

Question

MySQL

Good afternoon, I can’t set a time interval, how to do it, for example, search from 08/01/16 15:00 to 08/01/16. 17:00 ?

select h.name AS host_name, i.name AS item_name, i.key_ as item_key, FROM_UNIXTIME(hs.clock) AS last_time, hs.value, hs.logeventid
from 
  hosts h 
  join items i on h.hostid = i.hostid
  join history_log hs on i.itemid = hs.itemid
where hs.clock > UNIX_TIMESTAMP() - 86400 and i.hostid = '10458' and hs.logeventid = '4624' and hs.logeventid = '4625'
order by last_time, item_name 
INTO OUTFILE '/tmp/logon_logoff.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Answer 1

maybe so:

... 
hs.clock  between str_to_date('01.08.16 15:00','%d.%m.%Y %H:%i') 
and str_to_date('01.08.16 17:00','%d.%m.%Y %H:%i') 
...

Answer 2

i think it will look like this

select h.name AS host_name, i.name AS item_name, i.key_ as item_key, FROM_UNIXTIME(hs.clock) AS last_time, hs.value, hs.logeventid
from 
  hosts h 
  join items i on h.hostid = i.hostid
  join history_log hs on i.itemid = hs.itemid
where 
   DATE_FORMAT(FROM_UNIXTIME(hs.clock/1000), '%Y-%m-%d %H:%i:%s') 
BETWEEN STR_TO_DATE('2016-08-01 15:00:00','%Y-%m-%d %H:%i:%s') 
AND STR_TO_DATE('2016-08-01 17:00:00','%Y-%m-%d %H:%i:%s') 
and i.hostid = '10458' and hs.logeventid = '4624' and hs.logeventid = '4625'
order by last_time, item_name 
INTO OUTFILE '/tmp/logon_logoff.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Answer 3

FROM_UNIXTIME -- goodbye indexes.
Use native DATETIME as Evgeny Voinov wrote and you will be happy :)