Discussion on using floating point numbers to operate Oracle Database dates

As we all know, Oracle time can be directly used for addition and subtraction operations, such

Select sysdate, sysdate-2 from dual "SYSDATE" "SYSDATE-2" ----------------------------------- "19-5-09" "17-5-09"

Obviously, sysdate-2 indicates the date-2, that is, the time two days ago, and the time two days later + 2.

However, sometimes we don't need such a long time two days ago or two days later. We need two hours ago or two minutes ago.

However, I have not found any Oracle-related functions that can be directly operated by hour or minute.

Later, I found out that the date was a floating point number when I used it for addition and subtraction. So I did two experiments.

Lab 1:

select to_char(sysdate, 'yyyy-MM-dd hh24:mi:ss') cur_time, to_char(sysdate - 2 / 24, 'yyyy-MM-dd hh24:mi:ss') time_before_2_hour from dual; "CUR_TIME"                       "TIME_BEFORE_2_HOUR" ------------------------------------------------------------------- "2009-05-19 09:21:58"    "2009-05-19 07:21:58"

Lab 2:

select to_char(sysdate, 'yyyy-MM-dd hh24:mi:ss') cur_time, to_char(sysdate - 2 / (24 * 60), 'yyyy-MM-dd hh24:mi:ss') time_before_2_minutes from dual; "CUR_TIME"                      "TIME_BEFORE_2_MINUTES" --------------------------------------------------------------------- "2009-05-19 09:24:18"    "2009-05-19 09:22:18"

The results show that it is obvious that 2 hours ago is the first day, 2 minutes is the second day (24*60), the addition and subtraction of Oracle date or the addition and subtraction of day, however, it is a floating point number, so after being converted to a date, it is actually the addition and subtraction of time.

1. Character Processing skills in Oracle databases
2. Optimize Oracle tablespace design to improve database performance
3. Oracle Database disk I/O Optimization