CODEWITHSUNDEEP
mysqlruby-on-railsrubydatabase
Shefalee Chaudhary
Shefalee Chaudhary

Reputation: 602

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.

Upvotes: 3

Views: 86

Answers (4)

Moataz Zaitoun
Moataz Zaitoun

Reputation: 727

Try this :

User.where.not("Hour(created_at) >= '16:04' AND Hour(created_at) <= '11:02'")

Upvotes: 1

Perry
Perry

Reputation: 11700

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')")

Upvotes: 4

kiran madke
kiran madke

Reputation: 1

select * from time_table 
   where HOUR(time_column) BETWEEN 6 AND 11

Upvotes: 0

Sunil Patidar
Sunil Patidar

Reputation: 64

Try this query:

User.where("DATE_FORMAT(`created_at`,'%H:%i:%s') BETWEEN '11:00:00' AND '04:00:00'");

Upvotes: 0

Related Questions