Query on a time range ignoring the date of timestamps

Question

I'm trying to query a purchases table in my rails database (Postgres) and I want to query on time ranges.

For example, I'd like to know how many purchases were made between 2 PM and 3 PM across all dates.

There is a created_at column in this table but I don't see how to accomplish this without searching for a specific date as well.

I've tried:

Purchases.where("created_at BETWEEN ? and ?", Time.now - 1.hour, Time.now)

But this ultimately will just search for today's date with those times.

Answer 1

You need to extract just the hour portion from created_at using PostgreSQL's date_part/extract function.

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20

For example, something like this:

Purchases.where(["EXTRACT(HOUR FROM created_at) BETWEEN ? AND ?", 13, 14])