CODEWITHSUNDEEP
ruby-on-rails-3postgresqlactiverecord
Fabiano Soriani
Fabiano Soriani

Reputation: 8570

Rails AR query between hour and minute

Rails 3 app, pg adapter, my schema look as: 

create_table "events", :force => true do |t|
 t.integer  "event_type_id"
 t.datetime "start_at"
 #...
end

Some example data include:

p.events.each{|e| puts e.start_at }; 1
2009-03-23 08:30:00 UTC
2009-05-20 07:45:00 UTC
2009-05-20 07:45:00 UTC
2009-03-23 16:00:00 UTC
2009-05-20 10:00:00 UTC
2009-03-23 19:30:00 UTC
2009-03-23 11:30:00 UTC
2009-05-20 07:45:00 UTC
2009-05-20 07:45:00 UTC
2009-05-20 09:00:00 UTC
2009-05-20 07:45:00 UTC

I'd like to search in between the next 3 hours, but ignoring the day! Hour and minute is all that matters to me..
Is it possible to be done in Postgres? I've been trying to use pg EXTRACT(), but no success so far

I don't want to filter all dates in ruby code :(

Upvotes: 0

Views: 520

Answers (2)

Fabiano Soriani
Fabiano Soriani

Reputation: 8570

Ignoring the minutes, and only taking into account the hours:

#On my model    
scope :next_3_hours, ->(h){
  where( [" date_part( 'hour', \"start_at\") = ? OR date_part( 'hour', \"start_at\") = ? OR date_part( 'hour', \"start_at\") = ? ",h,h+1,h+2])
    .order("date_part( 'hour', \"start_at\") ASC, date_part( 'minute', \"start_at\")")
}

result:

# on SQL (considering now to be 17:00)
SELECT "events".* FROM "events"
WHERE ( date_part( 'hour', "start_at") = 17 
        OR date_part( 'hour', "start_at") = 18
        OR date_part( 'hour', "start_at") = 19 )
ORDER BY date_part( 'hour', "start_at") ASC, date_part( 'minute', "start_at")

Upvotes: 1

user272735
user272735

Reputation: 10648

I introduce only one way how this can be done in PostgreSQL. I've no RoR experience so I can't help you on that part.

The basic idea below is that all timestamps are converted to the same day so the hours and minutes can be compared:

  1. convert timestamps to strings that include only hours an minutes
  2. converting that string back to a timestamp causes the date part to be 1st Jan at year 1
create table test (id serial, start_at timestamp);

insert into test(start_at)
select * from generate_series('2008-03-01 00:00'::timestamp,
                              '2008-03-15 12:00', '5 hours');

with 
hh24mi_now as (select to_timestamp(to_char(current_timestamp, 'HH24:MI'), 'HH24:MI'))
select * from test where id in (
  select id
    from test
   where to_timestamp(to_char(start_at, 'HH24:MI'), 'HH24:MI') >= (select * from hh24mi_now)
     and to_timestamp(to_char(start_at, 'HH24:MI'), 'HH24:MI') < (select * from hh24mi_now) + interval '3 hours'
  )
order by id
;

Result of an example run:

 id |      start_at       
----+---------------------
 13 | 2008-03-03 12:00:00
 18 | 2008-03-04 13:00:00
 23 | 2008-03-05 14:00:00
 37 | 2008-03-08 12:00:00
 42 | 2008-03-09 13:00:00
 47 | 2008-03-10 14:00:00
 61 | 2008-03-13 12:00:00
 66 | 2008-03-14 13:00:00

Upvotes: 1

Related Questions