How to take yesterdays datetime in Oracle with specific time?

Question

I need to take yesterdays value like:

18-11-2018 06:00:00

select (systimestamp - 1) as ts_bytes from dual;

where time is fixed.

Answer 1

I think you are looking for this:

select TRUNC(systimestamp - 1) + INTERVAL '6' HOUR as ts_bytes 
from dual;

or

TRUNC(systimestamp) - INTERVAL '18' HOUR

or if you prefer DATE values rather than TIMESTAMP

select TRUNC(SYSDATE - 1) + 6/24 as ts_bytes 
from dual;

Answer 2

If you want then time at midnight (00:00:00) of yesterday then truncate the current timestamp to the start of today and then subtract one day:

SELECT TRUNC(SYSTIMESTAMP) - INTERVAL '1' DAY as ts_bytes
FROM   DUAL;

If you want a specific HH:MM:SS time then add an interval with that time:

SELECT TRUNC(SYSTIMESTAMP) - INTERVAL '1' DAY + INTERVAL '12:34:56' HOUR TO SECOND as ts_bytes
FROM   DUAL;

Answer 3

Select to_char(trunc(sysdate-1),’dd-mm-yyyy’)||’ 06:00:00’ as tyme from dual;

This is funny but should work.

Answer 4

SELECT  SYSTIMESTAMP - INTERVAL '1' DAY   AS yesterday
FROM    dual;

try this.