Shulou(Shulou.com)06/01 Report--

Oracle sql statement that calculates the average time format

Select to_char ((to_date ('2019-07-01),' yyyy-mm-dd') + numtodsinterval (avg (begin_time_second), 'second')),' hh34:mi:ss') avg_begin_time,to_char ((to_date ('2019-07-01),' yyyy-mm-dd') + numtodsinterval (avg (end_time_second), 'second')) 'hh34:mi:ss') avg_end_time--2019-07-01 00:00:00 + numtodsinterval (average,' seconds') converted to date format Then convert it to the time character format from (select-- convert the working time to seconds to_char (a.actontime, 'hh34') * 3600 + to_char (a.actontime,' mi') * 60 + to_char (a.actontime, 'ss') as begin_time_second,-- convert the work time to seconds to_char (a.actofftime) 'hh34') * 3600 + to_char (a.actofftime,' mi') * 60 + to_char (a.actofftime, 'ss') as end_time_second from empworkdate a)

Knowledge point expansion: get the year, month, day, hour and second in oracle

The time I wrote sql will be finished at 15:18 on October 10, 2016. here is a comparison:

-- get annual select to_char (sysdate,'yyyy') from dual--2016select to_char (sysdate,'YYYY') from dual--2016-- get monthly select to_char (sysdate,'mm') from dual--10select to_char (sysdate,'MM') from dual--10-- get date select to_char (sysdate,'dd') from dual--10select to_char (sysdate,'DD') from dual--10-- get select to_char (sysdate) 'hh34') from dual--15select to_char (sysdate,'hh') from dual--03select to_char (sysdate,'HH') from dual--03select to_char (sysdate,'HH24') from dual--15-- get minute select to_char (sysdate,'MI') from dual--14select to_char (sysdate,'mi') from dual--14-- get second select to_char (sysdate,'ss') from dual--35select to_char (sysdate 'SS') from dual--40-- acquires select to_char (sysdate,'yyyy-mm-dd hh:mi:ss') from dual--2016-10-10 03:17:25select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') from dual--2016-10-10 15:17:51

(1) from the above test SQL, we can see that the case of the year, month, day, yyyy,mm,dd,hh,mi,ss has no effect on obtaining the year, month, day, hour, minute and second from the oracle.

(2) for the case where the hour is divided into 12 hours and 24 hours, if you want to get the time that shows the afternoon, you can use hh34, which will show something similar to 15:30 instead of 03:30

Summary

The above is the sql statement of Oracle to calculate the average time format, which is introduced by Xiaobian. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.