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.
CodePudding user response:
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.
CodePudding user response:
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.