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.
- Extract the data, converting the UTC timestamps to CST
- 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.