SYSTIMESTAMP
To have a timestamp as of now, we can use the function SYSTIMESTAMP.
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-AUG-23 04.36.54.934270 PM -04:00
As you can see, we have precise date, time and time zone in Oracle. This is a very typical presentation of a timestamp.
N Days Before
To get a timestamp which is some days before now, we can make some tests. In the following test, we try to use it just like we used to do to the function SYSDATE.
SQL> select systimestamp - 3 from dual;
SYSTIMESTAMP-3
-------------------
2023-08-06 16:37:06
It seems that it casts to the type of datetime, then do the deduction. This is not a timestamp.
INTERVAL
To get a timestamp from now, we should use INTERVAL clause to operate SYSTIMESTAMP.
SQL> select systimestamp - interval '3' day from dual;
SYSTIMESTAMP-INTERVAL'3'DAY
---------------------------------------------------------------------------
06-AUG-23 04.37.17.599099000 PM -04:00
That's what we want precisely.