title: How to convert a timestamp from one timezone to other
body: <p>I have a table with TIMESTAMP column in Teradata.</p>
  
  <p>I want to consider the values stored in this column as if they are from 'America Pacific' timezone and convert it into GMT timestamp.</p>
  
  <p>I tried,</p>
  
  <pre><code>select  timestamp_col,
      timestamp_col at 'GMT' timestamp_col_gmt,
      timestamp_col at 'America Pacific' timestamp_col_pac,
  from table_name;
  
        timestamp_col          timestamp_col_gmt          timestamp_col_pac
  -------------------  -------------------------  -------------------------
  2014-10-27 22:02:29  2014-10-27 22:02:29+00:00  2014-10-27 15:02:29-07:00
  2013-11-28 22:55:27  2013-11-28 22:55:27+00:00  2013-11-28 14:55:27-08:00
  2014-09-19 00:23:56  2014-09-19 00:23:56+00:00  2014-09-18 17:23:56-07:00
  2013-06-18 00:39:18  2013-06-18 00:39:18+00:00  2013-06-17 17:39:18-07:00
  </code></pre>
  
  <p>But it looks like, it is considering timestamp_col as originally being in GMT.</p>
  
  <p>What I want is something like this.</p>
  
  <pre><code>      timestamp_col    timestamp_col_gmt  
  -------------------  -------------------
  2013-11-28 22:55:27  2013-11-29 06:55:27  --date belongs to PST. 8 hour difference
  2014-10-27 22:02:29  2014-10-28 05:02:29  --date belongs to PDT. 7 hour difference
  2014-09-19 00:23:56  2014-09-19 07:23:56  --date belongs to PDT. 7 hour difference
  2013-06-18 00:39:18  2013-06-18 07:39:18  --date belongs to PDT. 7 hour difference
  </code></pre>
  
  <p>I want to consider daylight savings also.</p>
  
  <p>Using dnoeth's query, for sometime during the timezone switch, the result is incorrect.</p>
  
  <pre><code>with recursive y(timestamp_col) as
  (
      select timestamp_val
      from x
      union all
      select timestamp_col + interval '1' hour
      from y
      where timestamp_col &lt;= timestamp'2015-03-08 10:00:00'
  ),
  x(timestamp_val) as 
  (
      select timestamp'2015-03-08 00:00:00'
  )
  select timestamp_col,
  cast(
      (timestamp_col at 'America Pacific') 
      - (extract(timezone_hour from (timestamp_col at 'America Pacific')) * interval '1' hour) 
  as timestamp(0))    timestamp_col_gmt
  from y
  order by timestamp_col;
  
        timestamp_col    timestamp_col_gmt
  -------------------  -------------------
  2015-03-08 00:00:00  2015-03-08 08:00:00    --PST, correct
  2015-03-08 01:00:00  2015-03-08 09:00:00    --PST, correct
  2015-03-08 02:00:00  2015-03-08 10:00:00    --Can be ignored
  2015-03-08 03:00:00  2015-03-08 11:00:00    --PDT, should be 10:00:00
  2015-03-08 04:00:00  2015-03-08 12:00:00    --PDT, should be 11:00:00
  2015-03-08 05:00:00  2015-03-08 13:00:00    --PDT, should be 12:00:00
  2015-03-08 06:00:00  2015-03-08 14:00:00    --PDT, should be 13:00:00
  2015-03-08 07:00:00  2015-03-08 15:00:00    --PDT, should be 14:00:00
  2015-03-08 08:00:00  2015-03-08 16:00:00    --PDT, should be 15:00:00
  2015-03-08 09:00:00  2015-03-08 17:00:00    --PDT, should be 16:00:00
  2015-03-08 10:00:00  2015-03-08 17:00:00    --PDT, correct
  2015-03-08 11:00:00  2015-03-08 18:00:00    --PDT, correct
  
  with recursive y(timestamp_col) as
  (
      select timestamp_val
      from x
      union all
      select timestamp_col + interval '1' hour
      from y
      where timestamp_col &lt;= timestamp'2015-11-01 10:00:00'
  ),
  x(timestamp_val) as 
  (
      select timestamp'2015-11-01 00:00:00'
  )
  select timestamp_col,
  cast(
      (timestamp_col at 'America Pacific') 
      - (extract(timezone_hour from (timestamp_col at 'America Pacific')) * interval '1' hour) 
  as timestamp(0))    timestamp_col_gmt
  from y
  order by timestamp_col;
  
        timestamp_col    timestamp_col_gmt
  -------------------  -------------------
  2015-11-01 00:00:00  2015-11-01 07:00:00    --PDT, correct
  2015-11-01 01:00:00  2015-11-01 08:00:00    --PDT, correct
  2015-11-01 02:00:00  2015-11-01 09:00:00    --PST, should be 10:00:00
  2015-11-01 03:00:00  2015-11-01 10:00:00    --PST, should be 11:00:00
  2015-11-01 04:00:00  2015-11-01 11:00:00    --PST, should be 12:00:00
  2015-11-01 05:00:00  2015-11-01 12:00:00    --PST, should be 13:00:00
  2015-11-01 06:00:00  2015-11-01 13:00:00    --PST, should be 14:00:00
  2015-11-01 07:00:00  2015-11-01 14:00:00    --PST, should be 15:00:00
  2015-11-01 08:00:00  2015-11-01 15:00:00    --PST, should be 16:00:00
  2015-11-01 09:00:00  2015-11-01 17:00:00    --PST, correct
  2015-11-01 10:00:00  2015-11-01 18:00:00    --PST, correct
  2015-11-01 11:00:00  2015-11-01 19:00:00    --PST, correct
  </code></pre>