PostgreSQL AT TIME ZONE using JOIN table
How to use the method AT TIME ZONE
when using a join 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 am taking times reservations.start_at
and end_at
, and I want to convert them to a different time zone ( time_zone_name
in a joined table)
Every booking has a trip and thus has time_zone_name
. I want to apply this trip time_zone_name
to start_ at
and booking points end_at
. If I do this:
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 a reason. The only difference between this second request and the first original request is that the second request has no 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 dates are stored in UTC zero in PG (PostgreSQL 9.3.5). My goal is to pass the "start_at" and "end_at" timestamps to my scope and return the correct rides. Trips can be stored in different time zones (they have a column for utc_offset and time_zone_name, time_zone_name is "America / Los_Angeles", etc.), and reservations owned by them (showing "has_many") have start and end times stored in utc zero also.
My goal is to pass date and time (no timezone) as the "start_at" and "end_at" parameters for the scope. Here's an example: I want to find all rides that have no reservations at 8 am to 9 am compared to the ride time zone (travel time). This means that I cannot just search the DB for conflicts with two specific start and end time stamps, because they 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 time stamps (for example 7/10/2015 8 AM PST - 7/10/2015 9 AM PST), I may end up with reserves that happen at the same time they happen at a different relative time (bookings can happen at the same time as printing 10/10/2015 8am PST - 7/10/2015 9am PST.however, since the trip is in EST, the booking should not count as a collision as we are looking for rides available between 8-9am local travel time). To be honest, I keep everything in UTC as the standard, but I will treat some timestamps as "dates without time" because they need to be converted from "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 parameters with reservations start_at and end_at times. I should be able to take all the start and end times of the booking (stored in zero UTC), convert them to my local time in the ride time zone (using "AT TIME ZONE rides.time_zone_name") and then ":: timestamp 'value to get only the date and time without the timezone.This local time can be compared to the "relative" start / end time of UTC zero time that I supply to the area.
source to share
Rails ActiveRecord converts all timestamps and timestamps - which are synonymous with Rails - to UTC to avoid running timezone-specific logic in the database. This applies to storage as well as queries for values. Thus, you should be able to define the scope without the timezone 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 used the PostgreSQL function tsrange
for non-zonal time ranges and that '[]'
the last argument indicates that they are included ranges, i.e. the end points are part of the range. For exclusive ranges use '()'
.
source to share