How to get 24 hours back time from current time in Oracle

Question

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.

Answer 1

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;

Answer 2

Half the problem is that whatever client program is being used to display the values is using the default date format for their territory and that default format is set to DD-MON-RR.

You can change the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT session parameters and that will (assuming your client program uses them and not some internal settings) give you the output you are expecting:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9TZR';

Then

SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;

Outputs (depending on your system time zone):

SYSDATE             | SYSTIMESTAMP                       
:------------------ | :----------------------------------
2020-11-30 10:12:22 | 2020-11-30 10:12:22.476282000+00:00

If you use SYSTIMESTAMP-1 then Oracle does not support subtracting a NUMBER data type from a TIMESTAMP [WITH TIME ZONE] data type but it does support subtracting a NUMBER data type from a DATE data type and will perform an implicit cast from TIMESTAMP to DATE so that the query is valid.

For example:

SELECT SYSDATE - 1, SYSTIMESTAMP - 1, SYSTIMESTAMP - INTERVAL '1' DAY FROM DUAL;

Outputs:

SYSDATE-1           | SYSTIMESTAMP-1      | SYSTIMESTAMP-INTERVAL'1'DAY        
:------------------ | :------------------ | :----------------------------------
2020-11-29 10:24:02 | 2020-11-29 10:24:02 | 2020-11-29 10:24:02.651735000+00:00

You can see that in the middle column SYSTIMESTAMP-1 gives the same output as SYSDATE-1 but in the right-hand column, subtracting an interval has ensured that TIMESTAMP WITH TIME ZONE data type is maintained.

So your query:

SELECT * FROM user WHERE created_date <  SYSTIMESTAMP-1

Is effectively:

SELECT * FROM user WHERE created_date <  CAST( SYSTIMESTAMP AS DATE )-1

Which will have exactly the same year, month, day, hour, minute and (integer) second components but will lose the fractional seconds and time zone information from the SYSTIMESTAMP.

If your column does not have time zone data and the level of precision in the fractional seconds does not matter to you then your query will work adequately.

However, if you want to keep the time zone and/or fractional seconds information then you can use:

SELECT * FROM user WHERE created_date <  SYSTIMESTAMP - INTERVAL '1' DAY;

However, if created_date is a DATE column, you probably want:

SELECT * FROM user WHERE created_date <  SYSDATE - INTERVAL '1' DAY;

or

SELECT * FROM user WHERE created_date <  SYSDATE - 1;

db<>fiddle here

Answer 3

You can use it as (SYSDATE, -1). Also, if you want to search for new records within 24 hours, it should be "created_date> = (sysdate-1)".