GoldenGate 12.2 Tutorial: Casting Timestamp Data in Oracle to Local Time Zone

Oracle GoldeGate 12.2 recently presented me with a challenge.  I implemented the GoldenGate 12.2 internal heartbeat mechanism.  It all worked well, but when I went to select the heartbeat history it was in UTC time, since my database is set to UTC.  Well, I wanted to see the data in my own timezone (US Central).

 

After a few hours of research and some help from some co-workers I came up with the following that seems to work.  The heartbeat_received_ts is of type TIMESTAMP(6).

SELECT CAST(FROM_TZ(CAST(heartbeat_received_ts AS TIMESTAMP), ‘UTC’) AT TIME ZONE ‘America/Chicago’ AS Date)

‘ CT’ as incoming_heartbeat_ts, incoming_path, incoming_lag FROM gg_lag_history;

This allows me to see the data in my own timezone, thus avoiding confusion.