Oracle SQL Query - Subtract Hours

Question

Hello, I’m a beginner in SQL, and I’m in mortal doubt. This query is to analyze an execution log on a basis of my company. The problem is that the time zone of the OS is +0.00 which creates incorrect records in my log (it is not possible to make this change, so I already ruled out the possibility). I need to subtract three hours of a timestamp field and I have no idea how to execute it. I also perform a Substring to collect the date, start time and end time of the execution. Maybe it would be a solution to shorten the three hours directly from that substring, but I don’t know how I can perform that. You would also need to do the hours treatment for example an execution at one o'clock in the morning log when subtracting should be 22 hours. Someone could help me?

  SELECT DISTINCT
SUBSTR(R.RUNSTARTTIMESTAMP,10, 2) "HORA START",
SUBSTR(R.RUNSTARTTIMESTAMP,13,2) "MINUTO START",
SUBSTR(R.RUNENDTIMESTAMP,10, 2) "HORA END",
SUBSTR(R.RUNENDTIMESTAMP,13,2) "MINUTO END",
SUBSTR(R.RUNSTARTTIMESTAMP,1,8) "data",
SUBSTR(R.RUNSTARTTIMESTAMP,9, 9) "STARTTIME",
SUBSTR(R.RUNENDTIMESTAMP,9, 9) "ENDTIME",
R.RUNSTARTTIMESTAMP,
R.RUNENDTIMESTAMP,
R.ELAPSEDRUNSECS


FROM
DSODB.JobExec J,
DSODB.JobRun R,
DSODB.JobRunLog L,
DSODB.LogTypeRef T


WHERE J.ProjectName = 'MDM_PRD'

AND J.JobName = 'Sq_Ctr_MdmPes_BipPct'
AND R.JOBID = J.JOBID
AND L.RUNID = R.RUNID
AND L.LogType = T.LogTypeCode

AND  r.runstarttimestamp >= to_timestamp('06-08-2018 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
ORDER BY r.runstarttimestamp
ASC;

Answer 1

You can use interval to do this, be it so:

select R.RUNSTARTTIMESTAMP - numtodsinterval(3,'hour') from DSODB.JobRun R

or so:

select R.RUNSTARTTIMESTAMP - interval '3' hour from DSODB.JobRun R

See here an example working: http://sqlfiddle.com/#! 4/5e0e9/8