What does Oracle SYSDATE - 1/8 mean?

Question

I am looking at a query:

select JOB_ID from db where last_updated_date >= sysdate - 1/8

I went through the ORACLE SYSDATE documentation but couldn't understand what sysdate - 1/8 means.

Please clarify.

Answer 1

In Oracle, it means you're subtracting 1/8 of the whole day. As one day has 24 hours, its 1/8th part is 24/8 = 3 hours. So:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate col1,
  2         sysdate - 1/8 col2
  3  from dual;

COL1                COL2
------------------- -------------------
29.10.2021 22:18:36 29.10.2021 19:18:36

SQL>

• date value remained the same (it is still today, 29.10.2021)
• time has changed; right now, it is 22:18:36. When we subtract 3 hours from it, we get 19:18:36 (3 hours earlier)

It means that your query fetches rows whose last_updated_date column value is within the last 3 hours.

Answer 2

It will subtract 3 hours (24 hours/8) from sysdate, try running the following:

select to_char((sysdate - 1/8), 'mm/dd/yyyy hh24:mi:ss') from dual

This will give you 3 hours previous to the current date/time of the database.