3

How do I use the AT TIME ZONE method while using a joins table?

scope :not_reserved_between, ->(start_at, end_at) { ids = Reservation.joins(:ride).where(":e >= (reservations.start_at AT TIME ZONE rides.time_zone_name)::timestamp AND :s <= (reservations.end_at AT TIME ZONE rides.time_zone_name)::timestamp", s: start_at.utc, e: end_at.utc).pluck(:ride_id); where('rides.id not in (?)', ids) }

In this query I'm taking the reservations.start_at and end_at times and I want to convert them to a different timezone (the time_zone_name in the joined rides table)

Every reservation has a ride and will thus have a time_zone_name. I want to apply that ride's time_zone_name to the reservation's start_at and end_at stamps. If I do:

scope :not_reserved_between, ->(start_at, end_at) { ids = Reservation.joins(:ride).where(":e >= (reservations.start_at AT TIME ZONE rides.time_zone_name)::timestamp AND :s <= (reservations.end_at)::timestamp", s: start_at.utc, e: end_at.utc).pluck(:ride_id); where('rides.id not in (?)', ids) }

This will work for some reason. The only difference between this second query and the first original query is that the second query does not have AT TIME ZONE rides.time_zone_name for reservations.ends_at.

Here is the table schema:

# == Schema Information
#
# Table name: reservations
#
#  id               :integer          not null, primary key
#  ride_id          :integer
#  start_at         :datetime
#  end_at           :datetime
#  created_at       :datetime
#  updated_at       :datetime
#
# == Schema Information
#
# Table name: rides
#
#  id                       :integer          not null, primary key
#  user_id                  :integer
#  latitude                 :float
#  longitude                :float
#  created_at               :datetime
#  updated_at               :datetime
#  utc_offset               :integer
#  time_zone_name           :string(255)
#

All datetimes are stored in UTC zero in PG (PostgreSQL 9.3.5). My goal is to pass 'start_at' and 'end_at' timestamps to my scope and have the correct rides returned. Rides can be stored in different time zones (they have a column for utc_offset and time_zone_name, time_zone_name being 'America/Los_Angeles', etc) and the Reservations that belong to them (ride has_many reservations) have their start and end times stored in utc zero as well.

My goal is to pass a datetime (without timezone) as my 'start_at' and 'end_at' params for the scope. Here is an example: I want to find all rides that do not have reservations at 8am-9am relative to the ride's (the reservation's ride) timezone. This means I can't just search the DB for conflicts with two specific start and end timestamp because that will only find collisions with that exact time. Also this method can give me "false collisions": if I search for two specific start and end timestamps (say 7/10/2015 8am PST - 7/10/2015 9am PST) I may end up having collisions with Reservations that occur at the same time however they occur at a different relative time (the reservation may occur at the same time stamp as 7/10/2015 8am PST - 7/10/2015 9am PST however because the ride is located in EST the Reservation should not be counted as a collision as we are searching for rides available between 8-9am in the local time of the ride). To put it bluntly I am storing everything in UTC zero as a standard however I will be treating some timestamps as "datetimes without timezones" because they need to be converted from a 'relative time'.

scope :not_reserved_between, ->(start_at, end_at) { ids = Reservation.joins(:ride).where(":e >= (reservations.start_at AT TIME ZONE rides.time_zone_name)::timestamp AND :s <= (reservations.end_at AT TIME ZONE rides.time_zone_name)::timestamp", s: start_at.utc, e: end_at.utc).pluck(:ride_id); where('rides.id not in (?)', ids) }

^My thought process from the above code is that I am comparing the scope params with reservation start_at and end_at times. I should be able to take all of the reservation start and end times (stored in UTC zero), convert them to their local time in the ride's time zone (by using 'AT TIME ZONE rides.time_zone_name') and then '::timestamp' the value to receive just the datetime without the time zone. This local time can be compared to the 'relative' UTC zero start/end times I am supplying to the scope.

Eric Walsh
  • 3,035
  • 1
  • 16
  • 21
  • 3
    As for your question: It all depends on the exact data types of involved columns. A table definition would go a long way. Version number of your software would not hurt either. – Erwin Brandstetter Jul 10 '15 at 02:12
  • 2
    It would also help to know if your reservation times and your start/end times are in terms of local time or UTC. – Matt Johnson-Pint Jul 10 '15 at 04:32
  • @ErwinBrandstetter - the question has been updated with more relevant info, hopefully that makes my intention more clear. – Eric Walsh Jul 10 '15 at 16:23
  • @MattJohnson I've detailed out how I store time, please let me know if there is anything else I should clarify! – Eric Walsh Jul 10 '15 at 16:25
  • `This will work for some reason.` How exactly does the first statement not work? Do you get an error or incorrect results? What exactly do you pass for `:e` and `:s`? I assume `datetime` in Ruby is mapped to `timestamp` in Postgres, and not `timestamptz`? It would be ideal to see the actual Postgres table definition (what you get with `\d reservations` in psql) and the actual query that is sent to the Postgres server ([can be logged with the setting `log_statement = 'all'`](http://stackoverflow.com/a/8425131/939860)). – Erwin Brandstetter Jul 20 '15 at 15:11

1 Answers1

0

Rails ActiveRecord converts all datetimes and timestamps – which are synonymous in Rails – to UTC to avoid performing any time zone-dependent logic in the database. This applies to storing as well as querying values. As such, you should be able to define the scope without any time zone logic:

self.not_reserved_between(start_at, end_at)
  includes(:reservations).where.not("tsrange(reservations.start_at, reservations.end_at, '[]') && tsrange(?, ?, '[]')", start_at, end_at)
end

Note that I've made use of PostgreSQL's tsrange function for ranges of non-zoned times, and that the '[]' in the last argument indicates that they are inclusive ranges, i.e. that the endpoints are a part of the range. For exclusive ranges, use '()'.

eirikir
  • 3,802
  • 3
  • 21
  • 39