sqlruby-on-railsrubypostgresqlrails-activerecord

Query on a time range ignoring the date of timestamps

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.

Solution

  • 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])