title: Using the Oracle "at time zone" function in sql - problem and solution
body: <p>I have a table with a date column that I know is stored in GMT.  I have a procedure that accepts a date input and an account ID.  The procedure:
  1) gets the account ID timezone (stored in table account)
  2) determines the start and end range in GMT as follows:
     v_start_time := cast( from_tz( cast( i_date as timestamp ), v_tz ) at time zone c_gmt as date ); -- where i_date is input, v_tz is 'US/Eastern' or any other tzname from v$timezone_names, and c_gmt is the string 'GMT'
     v_end_time := v_start_time + 1; -- Add exactly one day to start date
  3) return sys_refcursor to caller as:</p>
  
  <pre><code>open o_cur for
  select gmt_col, some_value
  from my_table
  where account_id = i_account_id
      and gmt_col between v_start_time and v_end_time;
  </code></pre>
  
  <p>However, the developer would like both the gmt_date and the local time in the cursor.  First, I attempted to use the exact same conversion method as I had to determine v_start_time, that is:</p>
  
  <pre><code>open o_cur for 
  select gmt_col,
      cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone v_tz as date ) as local_time, some_value
  from my_table
  where account_id = i_account_id
      and gmt_col between v_start_time and v_end_time;
  </code></pre>
  
  <p>However, when compiled, this results in ORA-00905: missing keyword.  I attempted to add the single quotes around the "v_tz" like: chr( 39 ) || v_tz || chr( 39 ), but that doesn't work - the proc compiles, but when I open the cursor, I get ORA-01882: timezone region not found.  After a bit of experimentation, here are two solutions that allow "at time zone" to work smoothly in sql:</p>
  
  <p>SOLUTION 1:</p>
  
  <pre><code>open o_cur for
  select gmt_col,
      cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone ( select v_tz from dual ) as date ) as local_time, some_value
  from my_table
  where account_id = i_account_id
      and gmt_col between v_start_time and v_end_time;
  </code></pre>
  
  <p>SOLUTION 2:</p>
  
  <p>in package spec:</p>
  
  <pre><code>function echo( i_sound in varchar2 ) return varchar2;
  pragma restrict_references( echo, wnps, rnps, wnds, rnds );
  </code></pre>
  
  <p>in package body:</p>
  
  <pre><code>function echo( i_sound in varchar2 ) return varchar2 is begin return i_sound; end;
  </code></pre>
  
  <p>in procedure:</p>
  
  <pre><code>open o_cur for
  select gmt_col,
      cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone echo( v_tz ) as date ) as local_time, some_value
  from my_table
  where account_id = i_account_id
     and gmt_col between v_start_time and v_end_time;
  </code></pre>
  
  <p>Performance appears to be comparable for each.  The second solution hints at something I've started to do recently, which is to use functions to return "constants" with pragma restrict_references, so I can use the constant values flexibly between pl/sql and sql.  For example:</p>
  
  <p>function c_gmt return varchar2;
  pragma restrict_references( c_gmt, wnds, rnds, wnps, rnps );</p>
  
  <p>select * from v$timezone_names where tzabbrev = c_gmt;
  select c_gmt from dual;
  v_start_time := blah blah blah || c_gmt;
  etc...</p>