I recently ran into a situation where the data being extracted from an Oracle database was timestamped in UTC and the servers SYSDATE was time zoned specific. My goal was to do two things.

1. Extract the data, converting the UTC timestamps to CST
2. Only pull the previous hour from the data table.

Oracle has a whole slew of date functions so all I had to do was find the right ones.

Converting a values time stamp from UTC to CST (or any timezone) ended up being more complicated than I expected.

SELECT to_date(to_char((from_tz(td.dtmUTCTimeStamp, 'UTC') at time zone 'America/Chicago'),'YYYY-MM-DD HH:MI:SS PM'),'YYYY-MM-DD HH:MI:SS PM') AS dtmDateTime
FROM tabledata AS td

What is happening above is basically taking the UTC timestamp (td.dtmUTCTimeStamp) value, telling Oracle its a UTC timestamp and then converting it to the ‘America/Chicago’ (CST) timezone. The magic really happens with the ‘at time zone’ key words.

My second task was a bit more easy. Oracle has a nifty function called SYS_EXTRACT_UTC which will pull out the UTC time stamp from the date time (with timezone) passed to it. To make this dynamic I pass the function the SYSTIMESTAMP. I then subtract an hour (-1/24) from the extracted UTC time stamp.

SELECT *
FROM tabledata AS td
WHERE td.dtmUTCTimeStamp >= SYS_EXTRACT_UTC(SYSTIMESTAMP) - (1/24)

Good luck and may your data extracts be quick.