CODEWITHSUNDEEP
ruby-on-rails-3postgresqlruby-on-rails-4activerecord
Asnad Atta
Asnad Atta

Reputation: 4003

Rails business hours query for active record

I have two models

OfficeTimeing < ActiveRecord::Base
  belongs_to :office
end


Office < ActiveRecord::Base
  has_many :office_timings
end

with two fields opening_time and closing_time these fields have string values like "09:00 AM" I want a query how can I find currently open offices I want something like this.

Office.joins(:office_timings).where("Datetime.now > office_timings.opening_time AND office_timings.closing_time > DateTime.now")

Upvotes: 1

Views: 479

Answers (2)

hypern
hypern

Reputation: 887

I don't know how to compare two times represented as a string without being able to parse them in rails first, however, have you considered storing your opening and closing times in seconds (counting from midnight) rather than strings? That way you would easily be able to write this query.

UPDATE: A few useful methods to achieve this.

To get the current seconds since midnight for a specific time:

seconds = specific_time.seconds_since_midnight

To convert the seconds since midnight into a Time object:

time_instance = Time.at(seconds)

To produce a string in the form of 09:00 AM

time_instance.strftime("%I:%M %p")

Details about Time class.

Details about strftime.

Upvotes: 2

Kristj&#225;n
Kristj&#225;n

Reputation: 18813

You need to inject Datetime.now into your query using ?:

Office.joins(:office_timings)
  .where(
    "? > office_timings.opening_time AND office_timings.closing_time > ?",
    Datetime.now, Datetime.now
  )

Each ? will be safely replaced by the arguments following the query string.

You could also do this directly in Postgres using now(), and make the condition a bit easier to read using BETWEEN:

.where("now() BETWEEN office_timings.opening_time AND office_timings.closing_time")

Upvotes: 1

Related Questions