ebinmanuval ebinmanuval - 1 month ago 9
MySQL Question

Query to select record of last five days but in between a specific time

I need to select rows from

mdl_logstore_standard_log
, the condition is
timecreated
should be in between last five days 7.30 AM to 4.30PM. How can I achive the combination last 5 days and the time. This is what I have

SELECT * FROM mdl_logstore_standard_log
WHERE FROM_UNIXTIME(timecreated) >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
GROUP by userid


timecreated
is in unixtimestamp

Answer

You can do this way too

SELECT 
* 
FROM mdl_logstore_standard_log 
WHERE timecreated >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 5 DAY)
      AND ( 
          (timecreated % 86400) 
             BETWEEN UNIX_TIMESTAMP('1970-01-01 07:30') 
                 AND UNIX_TIMESTAMP('1970-01-01 16:30') 
      )
GROUP by userid

timecreated % 86400 would return the residue in seconds.

And if the residue lies between 1970-01-01 07:30 and 1970-01-01 16:30 then your condition is actually met.

Note:

  • Using GROUP BY without aggregate function is discouraged. (@scaisEdge already stated that)
  • If you approach this way you take advantage from index on timecreated field (if any)