CODEWITHSUNDEEP
mysqlsql
Ebin Manuval
Ebin Manuval

Reputation: 1255

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

Upvotes: 2

Views: 270

Answers (3)

1000111
1000111

Reputation: 13519

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)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You could use time 

SELECT * FROM mdl_logstore_standard_log 
WHERE FROM_UNIXTIME(timecreated) >= DATE_SUB(CURDATE(), INTERVAL 5 DAY) 
AND  time(FROM_UNIXTIME(timecreated))  
      between time('2016-01-01 07:30:00.0000' ) and  time('2016-01-01 16:30:00.0000' )
GROUP by userid

NB you are using group by without aggregation function .. this could retrive not coherent result ..

Upvotes: 0

AssenKhan
AssenKhan

Reputation: 566

PLS try this

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

Upvotes: 0

Related Questions