oracle-date plus minus

Addition

Select Sysdate,add_months (sysdate,12) from dual; --plus 1 years

Select Sysdate,add_months (sysdate,1) from dual; --Add January

Select Sysdate,to_char (sysdate+7, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 weeks

Select Sysdate,to_char (sysdate+1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 days

Select Sysdate,to_char (sysdate+1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 hours

Select Sysdate,to_char (sysdate+1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 minutes

Select Sysdate,to_char (sysdate+1/24/60/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 seconds

Subtraction

Select Sysdate,add_months (sysdate,-12) from dual; -Minus 1 years

Select Sysdate,add_months (sysdate,-1) from dual; --Minus January

Select Sysdate,to_char (sysdate-7, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 weeks

Select Sysdate,to_char (sysdate-1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 days

Select Sysdate,to_char (sysdate-1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 hours

Select Sysdate,to_char (sysdate-1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 minutes

Select Sysdate,to_char (sysdate-1/24/60/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 seconds

Oracle About time/date operations

1. Date-time interval operation

Time minus 7 minutes for the current time

Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual

The current time minus 7 hours of time

Select Sysdate-interval ' 7 ' hour from dual

The current time minus 7 days

Select Sysdate-interval ' 7 ' Day from dual

Time minus July for current time

Select Sysdate,sysdate-interval ' 7 ' month from dual

Time minus 7 years in the current time

Select Sysdate,sysdate-interval ' 7 ' year from dual

Time interval multiplied by a number

Select Sysdate,sysdate-8 *interval ' 2 ' hour from dual

2. Date-to-character manipulation

Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual

Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh:mi:ss ') from dual

Select Sysdate,to_char (sysdate, ' yyyy-ddd hh:mi:ss ') from dual

Select Sysdate,to_char (sysdate, ' yyyy-mm iw-d hh:mi:ss ') from dual

Refer to the related documentation for Oracle (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4. htm#48515)

3. Character to date operation

Select To_date (' 2003-10-17 21:15:37 ', ' yyyy-mm-dd hh24:mi:ss ') from dual

The concrete usage is similar to the to_char above.

4. Use of the Trunk/round function

Select Trunc (Sysdate, ' year ') from dual

Select Trunc (sysdate) from dual

Select To_char (trunc (sysdate, ' YYYY '), ' YYYY ') from dual

5.oracle data types with millisecond levels

--Returns the current time, month, day, hour, minute, millisecond

Select To_char (Current_timestamp (5), ' dd-mon-yyyy HH24:MI:SSxFF ') from dual;

--Returns the second millisecond of the current time, specifying the precision after seconds (Max =9)

Select To_char (Current_timestamp (9), ' Mi:ssxff ') from dual;

6. Calculate when the program is running (MS)

Declare

Type RC is REF CURSOR;

L_RC RC;

L_dummy All_objects.object_name%type;

L_start number default dbms_utility.get_time;

Begin

For I in 1.. 1000

Loop

Open L_RC for

' Select object_name from all_objects ' | |

' WHERE object_id = ' | | I

Fetch L_RC into l_dummy;

oracle-date plus minus