Oracle time addition/subtraction (clear Version)

Addition selectsysdate, add_months (sysdate, 12) fromdual; -- add 1 year selectsysdate, add_months (sysdate, 1) fromdual; -- add January selectsysdate, to_char (sysdate7, yyyy-mm-ddHH24: MI: SS) fromdual; -- add selectsysdate, to_char (sysdate1, yyyy-mm-dd

Addition select sysdate, add_months (sysdate, 12) from dual; -- add 1 year select sysdate, add_months (sysdate, 1) from dual; -- add January select sysdate, to_char (sysdate7, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 week select sysdate, to_char (sysdate1, 'yyyy-mm-dd

Addition

Select sysdate, add_months (sysdate, 12) from dual;-- Add 1 year

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

Select sysdate, to_char (sysdate + 7, 'yyyy-mm-dd HH24: MI: ss') from dual;-- Add 1 week

Select sysdate, to_char (sysdate + 1, 'yyyy-mm-dd HH24: MI: ss') from dual;-- Add 1 day

Select sysdate, to_char (sysdate + 1/24, 'yyyy-mm-dd HH24: MI: ss') from dual;-- Add 1 hour

Select sysdate, to_char (sysdate + 1/24/60, 'yyyy-mm-dd HH24: MI: ss') from dual;-- Add 1 minute

Select sysdate, to_char (sysdate + 1/24/60/60, 'yyyy-mm-dd HH24: MI: ss') from dual;-- Add 1 second

Subtraction

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

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 week

Select sysdate, to_char (sysdate-1, 'yyyy-mm-dd HH24: MI: ss') from dual;-- Minus 1 day

Select sysdate, to_char (sysdate-1/24, 'yyyy-mm-dd HH24: MI: ss') from dual;-- Minus 1 hour

Select sysdate, to_char (sysdate-1/24/60, 'yyyy-mm-dd HH24: MI: ss') from dual;-- Minus 1 minute

Select sysdate, to_char (sysdate-1/24/60/60, 'yyyy-mm-dd HH24: MI: ss') from dual;-- Minus 1 second

Oracle time/date operations

1. Date interval operation

Current Time minus 7 minutes

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

Current Time minus 7 hours

Select sysdate-interval '7' hour from dual

Current Time minus 7 days

Select sysdate-interval '7' day from dual

Current Time minus July

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

Current Time minus 7 years

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 operations

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 oracle documentation (ORACLE901DOC/SERVER.901/A90125/SQL _ELEMENTS4.HTM #48515)

3. Character-to-date operations

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

The usage is similar to that of to_char.

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 has millisecond-level data types

-- Returns the current time year month hour minute second millisecond

Select to_char (current_timestamp (5), 'DD-MON-YYYY HH24: MI: SSxFF') from dual;

-- Returns the second millisecond of the current time. The precision after the second can be specified (max = 9)

Select to_char (current_timestamp (9), 'mi: SSxFF ') from dual;

6. computing program running time (MS)

Declare

Type rc is ref cursor;

Rochelle rc;

Rochelle dummy all_objects.object_name % type;

Rochelle start number default dbms_utility.get_time;

Begin

For I in 1000

Loop

Open l_rc

'Select object_name from all_objects '|

'Where object_id = '| I;

Fetch l_rc into l_dummy;