Dealing with Oracle timestamp with timezone conversion

The Oracle scheduler stores dates as “timestamp with time zone”. This means that if the code creating the jobs doesn’t specify it the job will have the timezone of the client. This leads to scheduler tables appearing to have data out of order. This can confuse people, but is resolvable. You could recreate them with the same timezone (maybe modify them?), but I guarantee you Murphy will still show up one day and ruin it for everyone because I’m sure what has happened is that they have been created from various people’s workstation and thus have picked up different timezones.

So, another option is to just convert them to the timezone you want. It takes a little research, so here is how to do it easily:

select job_name
 , actual_start_date
 , run_duration
 , to_char(cast(actual_start_date as timestamp with time zone) at time zone 'UTC','yyyy/mm/dd hh24:mi:ss') start_date_utc
from dba_scheduler_job_run_details
where owner like 'THAT%'
order by 2
/

JOB_NAME ACTUAL_START_DATE RUN_DURATION * START_DATE_UTC *
------------------------------ -------------------------------------------------- -------------------- -------------------
LOAD_XXXXXX_FILE 03-DEC-14 03.01.58.370006 PM UTC +000 00:01:41 2014/12/03 15:01:58
XX_ETL_DAILY 03-DEC-14 08.30.00.558521 AM -07:00 +000 04:33:47 2014/12/03 15:30:00
LOAD_XXX_BILLING_DATA_TO_STAGE 03-DEC-14 09.30.00.586323 AM -06:00 +000 00:07:06 2014/12/03 15:30:00
LOAD_XXX_BILLING_DATA_TO_STAGE 03-DEC-14 09.37.06.535596 AM -06:00 +000 00:49:24 2014/12/03 15:37:06
LOAD_XXX_SPA_TO_CNTRL_DATA 03-DEC-14 09.26.30.598323 AM AMERICA/DENVER +000 00:00:04 2014/12/03 16:26:30
LOAD_XXX_CORP_TO_CNTRL_DATA 03-DEC-14 09.26.34.394084 AM AMERICA/DENVER +000 00:00:02 2014/12/03 16:26:34
FIX_XXX_DDP_CNTRL_DATA 03-DEC-14 09.26.35.922123 AM AMERICA/DENVER +000 00:00:00 2014/12/03 16:26:35
LOAD_XXXXXX_TERMINAL_DATA 03-DEC-14 09.26.36.115084 AM AMERICA/DENVER +000 00:09:30 2014/12/03 16:26:36
LOAD_XXXX_ADDL_DACEXPORT 03-DEC-14 10.36.05.635113 AM -06:00 +000 00:00:02 2014/12/03 16:36:05
LOAD_XXX_XXX_DATA 03-DEC-14 09.36.07.723682 AM AMERICA/DENVER +000 00:00:01 2014/12/03 16:36:07
LOAD_XXX_TERMINALS_DATA 03-DEC-14 09.36.08.357951 AM AMERICA/DENVER +000 01:34:15 2014/12/03 16:36:08
PUSH_XXX_AND_DDP_DATA_TO_HIST 03-DEC-14 11.10.22.989797 AM AMERICA/DENVER +000 00:00:26 2014/12/03 18:10:22
LOAD_BILLING_DATA 03-DEC-14 11.10.48.715428 AM AMERICA/DENVER +000 00:33:58 2014/12/03 18:10:48
JOB_CREATE_DUP_DATA 03-DEC-14 11.44.46.250927 AM AMERICA/DENVER +000 00:45:06 2014/12/03 18:44:46
LOAD_XXX_AUDIT_DATA 03-DEC-14 01.28.40.150046 PM AMERICA/DENVER +000 00:34:26 2014/12/03 20:28:40
LOAD_XXX_AUDIT_DATA 03-DEC-14 01.58.00.551371 PM AMERICA/DENVER +000 00:00:46 2014/12/03 20:58:00
LOAD_XXX_AUDIT_DATA 03-DEC-14 02.17.48.190868 PM AMERICA/DENVER +000 00:05:30 2014/12/03 21:17:48

One response to “Dealing with Oracle timestamp with timezone conversion

  1. Pingback: Dealing with Oracle timestamp with timezone conversion | Jed's

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s