Time Processing and time period Statistics in Oracle

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 Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12