Shefalee Chaudhary Shefalee Chaudhary - 1 month ago 6
Ruby Question

Fetch records within time stamp but not specific to date

I want to fetch records of users who signed in between 11:02 pm to 4:04 pm, date doesn't matter.

I tried something like :

User.where("TIME(created_at) BETWEEN '11:02:00' AND '04:04:00'")


But it's not working. Any help to this is greatly appreciated.

PS: Don't focus on Hour only. We also need data for particular minutes also.

Answer

The mysql between function works different than you expect.

The between function expect the first parameter to be the min value en the second parameter to be the max value. See the documentation: http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between

Try this:

User.where("(TIME(created_at) BETWEEN '23:00:00' AND '23:59:59' OR TIME(created_at) BETWEEN '00:00:00' AND '03:59:59')")

Based on your update it will be like this:

User.where("(TIME(created_at) BETWEEN '23:02:00' AND '23:59:59' OR TIME(created_at) BETWEEN '00:00:00' AND '04:03:59')")
Comments