This is how I converted a timestamp from UTC to a local time zone using SQL:

1
2
3
4
5
6
7

select   
  your_ts_value as time_stamp_at_source,  
  cast(from_tz(cast(to_date(your_ts_value,   
    'YYYYMMDDHH24MISS') as timestamp), 'UTC')  
    at time zone 'America/Los_Angeles' as date)   
    as time_stamp_at_local  
from your_table_with_a_ts_field;

A list of supported timezone names in Oracle can be found here.