Time Processing and time period Statistics in Oracle

Oracle time addition and subtraction due to the recent test work on time addition and subtraction, I found some algorithms on the Internet on time addition and subtraction, which can solve the pressing problem and hope

Oracle time addition and subtraction due to the recent test work on time addition and subtraction, I found some algorithms on the Internet on time addition and subtraction, which can solve the pressing problem and hope

Oracle time addition and subtraction

Recently, due to the time-frame addition and subtraction tests, I found some time addition and subtraction algorithms on the Internet, which can solve the pressing problem and help more Oracle beginners!

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 (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

Statistics by time period: note that you must change the SQL statement based on the actual type in Oracle:

Code Source:

Select to_char (trunc (sysdate) + trunc (dtime-trunc (sysdate) * 24*60/30) * 30/60/24), 'hh24: mi ') | '-' | to_char (trunc (sysdate) + trunc (dtime-trunc (sysdate) * 24*60/30 + 1) * 30/60/24 ), 'hh24: mi ') period, avg (v1), sum (v2), count (*) "ROWS" from test where trunc (dtime) = to_date (& day, 'yyyymmdd') group by to_char (trunc (sysdate) + trunc (dtime-trunc (sysdate) * 24*60/30) * 30/60/24), 'hh24: mi ') |'-'| to_char (trunc (sysdate) + trunc (dtime-trunc (sysdate) * 24*60/30 + 1) * 30/60/24), 'hh24: mi ') order by 1;

My modifications:

Select to_char (trunc (sysdate) +
Trunc (to_date (to_char (passvehicleinfo. passtime,
'Yyyymmdhh24: mi: ss '),
'Yyyymmdd hh24: mi: ss')-trunc (sysdate) * 24*60/30) * 30/60/24 ),
'Hh24: Mi') | '-' | to_char (trunc (sysdate) + trunc (to_date (to_char (passvehicleinfo. passtime, 'yyyymmdd hh24: mi: ss'), 'yyyymmdd hh24: mi: ss')-trunc (sysdate) * 24*60/30 + 1) * 30/60/24 ), 'hh24: Mi ')

From passvehicleinfo
Group by to_char (trunc (sysdate) +
Trunc (to_date (to_char (passvehicleinfo. passtime,
'Yyyymmdhh24: mi: ss '),
'Yyyymmdd hh24: mi: ss')-trunc (sysdate) * 24*60/30) * 30/60/24 ),
'Hh24: Mi') | '-' | to_char (trunc (sysdate) + trunc (to_date (to_char (passvehicleinfo. passtime, 'yyyymmdd hh24: mi: ss'), 'yyyymmdd hh24: mi: ss')-trunc (sysdate) * 24*60/30 + 1) * 30/60/24 ), 'hh24: Mi ')

For more information about Oracle, see the Oracle topic page? Tid = 12