I want to mark every record in the table with a TIMESTAMP WITH TIME ZONE value in UTC time. SYSDATE, of course, doesn't do timezones. SYSTIMESTAMP uses the server timezone, which is not UTC. CURRENT_TIMESTAMP uses the client timezone, which is not UTC. I could hardcode all my jobs to set $ENV{TZ} = 'UTC', but that seems fragile: somebody else could come along and wonder why in the world it's like that, or do something weird. Somebody could insert into the table through a program that failed to configure the TZ. I don't want to come back to a table with 3 or more different timezones in it, if I can help it, though since I'm using TIMESTAMP WITH TIME ZONE it would be fixable.
SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) is almost exactly what I need. It takes the timestamp and turns it into UTC. Unfortunately rather than returning a TIMESTAMP WITH TIME ZONE with the time zone set to UTC, it returns a plain TIMESTAMP, no timezone. So in the end, I go into the actual statement used to insert into the table (which if I'm smart is hidden in a package somewhere, or something) and set it to use FROM_TZ(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP), '00:00'). Wow. Seems like they could've just provided a function to do this, but at least it's possible.
