Subtract time using Oracle PL/SQL

Question

I'm creating a report using BI Publisher. Now, I want is to subtract 4 hours in the date. For example, the date I get in the oracle database is below,

2019-09-23T10:09:34.054+00:00

Now I want it to return in report using sql is,

2019-09-23T06:09:34.054+00:00

How can I do that?

Thanks!

Answer 1

Use INTERVAL?

SELECT ts - INTERVAL '4' HOUR
FROM yourTable;

Demo

If your source data is actually text, and not a bona fide timestamp column, then you may use TO_TIMESTAMP_TZ to first do a conversion:

SELECT
    TO_TIMESTAMP_TZ(REPLACE(s, 'T', ' ') 'YYYY-MM-DD HH24:MI:SS.FF3TZH:TZM') AS ts_original,
    TO_TIMESTAMP_TZ(REPLACE(s, 'T', ' ') 'YYYY-MM-DD HH24:MI:SS.FF3TZH:TZM') - INTERVAL '4' HOUR AS ts_offset
FROM yourTable;

Answer 2

You just need to subtract a fraction of time. Like this for 4 hours ago:

select sysdate-1/6 from dual;