I have a column in my Oracle db which records the creation time of a user in the following format
30-NOV-20 11.49.11.000000000 AM
(TIMESTAMP(6) format).
What I wanted to do is select all records whose creation time is 24 hours earlier than current time
So what I was going to do was subtract 1 from current time and compare it. But when I subtract 1 it returns only the date.
select * from user where created_date < SYSTIMESTAMP-1
dbms_output.put_line (SYSTIMESTAMP-1);
-->29-NOV-20
The time parts are missing which makes me unable to compare with created time in the table Please help me to complete this task.
If you subtract 1
from a date
datatype (e.g. sysdate
), it'll move you back one day. But, if you subtract it from a timestamp
datatype value, Oracle will convert it to date and return a date (moreover, it'll be truncated).
See the following example:
SQL> select
2 systimestamp val1,
3 systimestamp - 1 val2,
4 --
5 systimestamp - interval '1' day val3
6 from dual;
VAL1
-----------------------------------------------------
VAL2
--------
VAL3
-----------------------------------------------------
30.11.20 09:55:01,439352 +01:00
29.11.20
29.11.20 09:55:01,439352000 +01:00
SQL>
So, what you should do is to subtract an interval, i.e.
select *
from user
where created_date < systimestamp - interval '1' day;
It won't be truncated as per the
TRUNC
function; it will just lose the fractional seconds. The rest of the time part of the timestamp will still be present. The OP's NLS (or IDE) settings are just not displaying the time component.