Search code examples
sqlruby-on-railspostgresqlrails-activerecord

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


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.


Solution

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