Hello, I am a beginner in SQL and I have a mortal doubt. This query is to parse a running log on a basis of my company. The problem is that the OS time zone is +0.00 which creates incorrect logs in my log (you can not make this change, so I've already discarded the possibility). I need to subtract 3 hours from a Timestamp field and I have no idea how to run it. I also perform a Substring to collect the date, start time and end time of execution. Maybe it would be a solution to shorten the 3 hours directly from that substring, but I'm not sure how I can accomplish this. Also it would need to do the treatment of hours for example an execution to one hour of the morning in the log when subtracting it should be 22 hours. Could someone 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;