CODEWITHSUNDEEP
ruby-on-railsrubydatetimewhere-clause
sakthivel
sakthivel

Reputation: 71

How to get data which falls on specific days

Hi I've start_date column which capture time stamp, I'd like to query the data based on days like if date falls on sunday or monday like that.

For example 

from_date = Tue Nov 22 23:00:00 UTC 2016
to_date = Sun Nov 27 02:00:00 UTC 2016

Here I need get all the records from tuesday 23:00 pm to sunday 02:00 am.

Upvotes: 0

Views: 56

Answers (1)

Zeyu
Zeyu

Reputation: 118

Updated Answer

MySQL has dayofweek function for this.

You can write your query as:

MyModel.where('dayofweek(start_date) = ?', 1) # 1 is Sunday

To get all models which start_date is on Sunday.

To achieve your goal, you can query start_date on Tuesday AND start_date hour is later than 23:00, start_date on Wed, start_date on Thurs and etc.

So the final query would be like:

MyModel.where('(dayofweek(start_date) = ? AND hour(start_date) > ?) 
OR dayofweek(start_date) = ? OR dayofweek(start_date) = ? OR
dayofweek(start_date) = ? OR dayofweek(start_date) = ? OR
(dayofweek(start_date) = ? AND hour(start_date) > ?)'
, 3, 23, 4, 5, 6, 7, 1, 2)

Previous Answer

You can use a range query with where:

Model.where(start_date: from_date..to_date)

Upvotes: 1

Related Questions