Oracle timestamp added value
During my work, I encountered this problem. The session table needs to use the timestamp field. When the timestamp field is updated, the question is displayed. For example, the validity period of the session needs to be increased by 1 hour.
Using iimestamp + 1/24 will lose the precision of the second, and it will become the date type after the increase. After research, we found that oracle has a function called NUMTODSINTERVAL, which can solve this problem. The following two examples illustrate how to use this function.
Add one hour
SELECT to_char (systimestamp + NUMTODSINTERVAL (1, 'hour'), 'yyyy-mm-dd HH24: mi: ss: ff'), to_char (systimestamp, 'yyyy-mm-dd HH24: mi: ss: ff') FROM dual;
Add one minute
SELECT to_char (systimestamp + NUMTODSINTERVAL (1, 'minut'), 'yyyy-mm-dd HH24: mi: ss: ff'), to_char (systimestamp, 'yyyy-mm-dd HH24: mi: ss: ff') FROM dual;
The original version of numtodesignterval is described as follows:
NUMTODSINTERVAL converts n to an interval day to second literal. the argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. the argument interval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype. the value for interval_unit specifies the unit of n and must resolve to one of the following string values:
- 'Day'
- 'Hour'
- 'Minute'
- 'Second'
In terms of interpretation, the NUMTODSINTERVAL function can also increase the values of day and sesond. (to reduce the processing value, you only need to add "-" before the number.) It is a very powerful function. In addition, if you want to add a year or month,
Select the NUMTOYMINTERVAL function.