Filtering datetime not working when using timezone

Question

In my setup I want to filter Schedule by Activity, City, Date, Starting Time and End Time using select menus.

I've made it work with Ransack:

# db
create_table "schedules", force: :cascade do |t|
  t.integer  "city_id"
  t.integer  "activity_id"
  t.datetime "starts_at"
  t.datetime "ends_at"
  ...
end

# Schedule model
scope :six_hours_from_now, -> { where('starts_at >= ?', Time.zone.now + 6.hours) }

ransacker :start_date, type: :date do
  Arel.sql('starts_at::date')   # filter only the date from starts_at attribute
end

ransacker :start_time, type: :time do
  Arel.sql('starts_at::time')   # filter only the time from starts_at attribute
end

ransacker :end_time, type: :time do
  Arel.sql('ends_at::time')     # filter only the time from ends_at attribute
end

# Schedule controller
def index
  @q          = Schedule.ransack(params[:q])
  @q.sorts    = ['starts_at asc', 'ends_at asc'] if @q.sorts.empty?
  @schedules  = @q.result.six_hours_from_now
end

# view
<%= search_form_for @q do |f| %>
  <%= f.collection_select :activity_id_eq, @activities, :id, :name, { include_blank: 'All' } %>
  <%= f.collection_select :city_id_eq, @cities, :id, :name, { include_blank: 'All' } %>
  <%= f.select :start_date_eq, options_for_select([
        ['Today', Time.zone.today],
        ['Tomorrow', Time.zone.tomorrow],
        [(Time.zone.today + 2).strftime("%d %b"), Time.zone.today + 2],
        [(Time.zone.today + 3).strftime("%d %b"), Time.zone.today + 3],
        [(Time.zone.today + 4).strftime("%d %b"), Time.zone.today + 4],
        [(Time.zone.today + 5).strftime("%d %b"), Time.zone.today + 5],
        [(Time.zone.today + 6).strftime("%d %b"), Time.zone.today + 6]
      ], @q.start_date_eq), { include_blank: true } %>
  <%= f.time_select :start_time_gteq, ampm: true, default: { hour: '07' } %>
  <%= f.time_select :end_time_lteq, ampm: true, default: { hour: '23', minute: '45' } %>

  <%= f.submit "Filter" %>
<% end %>

Unfortunately the filters don't work anymore when I put in some code to handle time zone:

# application.rb
default settings, no changes

# application_controller.rb
before_filter :set_time_zone

def set_time_zone
  if current_user
    Time.zone = current_user.time_zone
  elsif current_admin
    Time.zone = current_admin.time_zone
  end
end

If I filter only on city, I got this query:

SELECT "schedules".* 
FROM "schedules" 
WHERE (
    "schedules"."city_id" = 6 AND 
    starts_at::time >= '2015-09-30 23:00:00.000000' AND 
    ends_at::time <= '2015-10-01 15:45:00.000000'
) AND (
    starts_at >= '2015-10-01 20:52:39.866880'
) ORDER BY "schedules"."starts_at" ASC, "schedules"."ends_at" ASC`

Basically if the user's time zone is GMT+8 then it will take the value from the starts_at and ends_at time select menu using today's date (1 Oct 2015) and automatically substract 8 hours from it before running the query. How do I prevent Ransack from substracting the offset hours?

Answer 1

Apparently the sql query automatically subtracting 8 hours according to my time zone is the expected behaviour and the right thing to do.

Rather than filtering data without any date involved, I removed the blank option from the date select and made sure the default schedules shown are filtered to tomorrow's date, rather than showing all the schedules. This way the user must choose a date first before filtering the schedules.

I had to modify the incoming params from the time select so it uses the params from the date select before handing all the params to Ransack.

Schedule model:

# Delete the custom Ransack methods as we don't need them anymore:

ransacker :start_date, type: :date ...
ransacker :start_time, type: :time ...
ransacker :end_time, type: :time ...

# ...and add these scopes:

scope :sort_by_datetime_asc, -> { order(:starts_at, :ends_at) }

def self.only_tomorrow
  where('starts_at BETWEEN ? AND ?', Time.zone.tomorrow.beginning_of_day, Time.zone.tomorrow.end_of_day)
end

Schedule controller:

params[:q] = {} unless params[:q]

# Copies the date params from date select to replace the date params from the time select menu.
if params["start_date"].present?
  date  = Time.zone.parse(params["start_date"])

  year  = date.strftime("%Y")
  month = date.strftime("%m")
  day   = date.strftime("%e")

  params[:q]["starts_at_gteq(1i)"] = year
  params[:q]["starts_at_gteq(2i)"] = month
  params[:q]["starts_at_gteq(3i)"] = day

  params[:q]["ends_at_lteq(1i)"]   = year
  params[:q]["ends_at_lteq(2i)"]   = month
  params[:q]["ends_at_lteq(3i)"]   = day
end

@q = Schedule.ransack(params[:q])

if params[:q].present?
  @schedules = @q.result
else
  @schedules = Schedule.only_tomorrow
end

@schedules = @schedules.six_hours_from_now.sort_by_datetime_asc

Schedule#index view:

<%= select_tag "start_date", options_for_select([...], selected: default_date_select) %>
<%= f.time_select :starts_at_gteq ...
<%= f.time_select :ends_at_lteq ...

Schedule helper:

# Show selected date for Schedule's date filter.
def default_date_select
  if params["start_date"].present?
    params["start_date"]
  else
    ['Tomorrow', Time.zone.tomorrow]
  end
end