Extract records from PostgreSQL based on time of day component of timestamp/DateTime in Rails Active Record

Question

I am working with a PostgreSQL database in a rails application and facing a challenge in extracting the user records on the basis of only the time component from timestamp type created_at column in my users table.

This query works fine:

users = User.where("    DATE_PART('hour',   created_at) >= ? 
                    AND DATE_PART('minute', created_at) >= ? 
                    OR  DATE_PART('hour',   created_at) <= ? 
                    AND DATE_PART('minute', created_at) <= ?", 
                    10, 0, 15, 30).pluck :created_at

But I'm curious if there's a more elegant approach to extract the desired records. Essentially, I aim to query records based solely on time, excluding the date. For instance, I want to retrieve all users created between 10:00 and 12:30 on any day (year, month and day do not matter).

I also tried this:

User.where("EXTRACT(HOUR FROM created_at) BETWEEN ? AND ?", 14, 15)

but it's only dealing with hours not with the minutes.

Desired time could be 10-12 hours or 10:00-12:30. How can I manage these minutes if I want to extract the records from 10:00 to 12:30?

In short, I'm seeking method where I can specify the desired time and obtain all records within those time slots.

Answer 1

PostgreSQL has a time type, that you can cast timestamp to in order to extract everything from hours down (not excluding seconds and fractions of seconds), in one go. Demo at db<>fiddle:

create table test(created_at timestamp);

--sample timestamps around every hour between now and 4 days from now
insert into test select g+random()*'1h'::interval 
from generate_series(now(),now()+'4 days','1h')_(g);

select created_at
from test
where created_at::time between '11:15' and '12:21:10.123';

created_at
2023-12-08 11:55:58.167576
2023-12-09 11:39:04.189425
2023-12-10 12:09:11.234004
2023-12-11 11:40:42.80833

From RoR, you can pass the time fields into PostgreSQL make_time(). The third field is a double precision, which allows you to pass seconds with fractions:

User.where("created_at::time BETWEEN make_time(?,?,?) 
                             AND     make_time(?,?,?)", 
           11, 15, 0, 
           12, 21, 10.123)

You can also pass any timestamp with a matching time, and cast to only use its time element:

User.where("created_at::time BETWEEN cast(:start_time as time) 
                             AND     cast(:end_time   as time)", 
           {start_time: Time.new(2023,12,07,11,15), 
            end_time: DateTime.new(1981,05,05,15,30)} )

It's possible to further speed this up by adding a functional index on created_at:

create index on test((created_at::time));