CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Chris Schmitz
Chris Schmitz

Reputation: 8257

How do you filter records by the time of day in Rails?

I have a bunch of records that have a DateTime stored on them that is only used to pull out the time of day (i.e. 6:00 p.m.) and I'd like to filter them by the time of day. For example, I'd like to get all the records where the time of day is between 10:00 a.m. and 2:00 p.m., regardless of what day that timestamp is for.

Is there a way to do this in Rails? I realize that DateTime might not be the easiest/best way to store this data, so I'm open to changing that. Also, I'm using Postgres.

Upvotes: 3

Views: 2578

Answers (2)

guilleva
guilleva

Reputation: 1311

With Postgres you can use typecasting to convert your datetime/timestamp fields to a time, for example:

select * from posts where created_at::time BETWEEN '10:00:00' and '14:00:00';

So, in Rails, you should be able to do:

Post.where('start_at::time BETWEEN ? AND ?', '10:00:00', '14:00:00')

And Postgres is smart enough to understand AM/PM format also:

Post.where('start_at::time BETWEEN ? AND ?', '10:00:00 AM', '02:00:00 PM')

Upvotes: 3

d_ethier
d_ethier

Reputation: 3911

If you're using the standard timestamps that are generated as part of the Rails model generator, then you're using DateTime for those timestamps.

Assuming that this is the created_at model attribute (it doesn't have to be, change it the value that you're using), here's how to do it:

In raw SQL, the query you're looking for is SELECT * FROM TABLE WHERE created_at BETWEEN FIRST_DATE AND SECOND_DATE; which can be represented in Rails as:

@first_date  = DateTime.new(now.year, now.month, now.day, 10, 0, 0, 0)
@second_date = DateTime.new(now.year, now.month, now.day, 14, 0, 0, 0)

Model.where(created_at: @first_date..@second_date)

The .. (which is a sequence operator) tells Rails to build the BETWEEN query using @first_date and @second_date as the FIRST_DATE and SECOND_DATE values in the raw SQL query.

Upvotes: 1

Related Questions