title: Optimize/Index Timezone Query
body: <p>I'm wondering if anyone has any recommendations on how to optimize this query so it can use an index?  We have reservations that are associated to properties in our database.  Each property has a timezone set.  What we want to query for is "all reservations that start on a given date (e.g. today)".</p>
  
  <p>The relevant query fragment is</p>
  
  <pre><code>reservations.start_on::Date = COALESCE((current_timestamp at time zone properties.time_zone), current_timestamp)::Date
  </code></pre>
  
  <p>The properties table is joined earlier in the query as you would expect.</p>
  
  <p>The problem is, as you probably guessed, timezones.  We can't simply ask for <code>start_on = {date}</code> because when we say <code>{date}</code> we really mean {date in that property's <code>timezone</code>} which may not actually be <code>{date}</code> based on the current time.  <code>{date}</code> will come in as, e.g. <code>4/7/2014</code> based on the timezone of our App server but we actually want to include reservations that start on <code>4/8/2014</code> if the reservation's property is in Sydney Australia.</p>
  
  <p>---- EDIT ----</p>
  
  <p>Some additional information.</p>
  
  <p>reservations.start_on is stored as type <code>date</code>.</p>
  
  <p>properties.time_zone is stored as <code>character varying(255), default 'America/Los_Angeles'::character varying.</code></p>
  
  <p>The reservation's start_on date is stored as the date were you in the timezone of the property.  The part that needs to be interpreted is the date we are querying for to shift it to the appropriate date in the various property timezones in most cases.</p>
  
  <p>I'm certainly open to other approaches but this was all I could think to do so far... Basically I want to be able to ask a few questions of the DB:</p>
  
  <ol>
  <li>What reservations are starting "today"?</li>
  <li>What reservations have start dates in the future?</li>
  <li>What reservations have start dates in the past?</li>
  </ol>
  
  <p>Start date, being a date though, is obviously actually a time range so you can't just say <code>start_on &gt; now()</code> for example because timezones.</p>
  
  <p>Table Definitions: <a href="https://gist.github.com/anonymous/10295380" rel="nofollow">https://gist.github.com/anonymous/10295380</a></p>
  
  <p>---- EDIT 2 ----</p>
  
  <p>I tried switching over to using tsrange for start_on.  The range is time shifted from the property's timezone to UTC.  That means a start_on of 4/11/2014 in Sydney is stored as <code>['4/10/2014 14:00:00', '4/11/2014 13:59:59']</code>.  I have a gist index on the tsrange version of the start_on column.</p>
  
  <p>This seems to work perfectly and return the right results.  It uses the gist index for @> queries with a specific time and is blazing fast.  Unfortunately it is NOT using the gist index for all range operations...</p>
  
  <p>Here is a gist (haha) with example EXPLAINs on some of our queries: <a href="https://gist.github.com/bdmac/10496601" rel="nofollow">https://gist.github.com/bdmac/10496601</a></p>
  
  <p>The column shown in the examples, <code>arrival_day</code> is equivalent to <code>start_on</code> but is a tsrange.  I have not removed the old <code>start_on</code> column yet.</p>
  
  <p>Some of the queries that are now slow are "upcoming" or "past" reservations where I had to construct a tsrange with no upper or lower bound depending.  I can't seem to figure out how to do a query with &lt;&lt; or >> that accepts an element instead of a range like you can do with @></p>