how to calculate the average of the time in th e HH:MM:SS format stored database table.. column contains hundreds of time values and need to table the avergae of it
my col look like,and column is declared as timestamp(6).
MY COL
------
1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00
[Code]...
I have a table with products (xPRODUCT), dates (xDATE) and parameters (xPARAMETER). Now I want to calculate the rolling 12 month average of the parameters over all products for all dates. I tried this:
select xDATE,
avg(xPARAMETER) over ( order by xDATE
range between numtodsinterval(365,'day')
preceding and current row )
[Code]....
... but this does not work. How can I do this?
from last two weeks we are receiving rhe below database alert on our second NODE (RAC ENV).
Global cache Average CR get time " is at .61789
Threshold is set on >.5 (M.Second).
me to figure this out because i really dont know the nature of this alert. what value i can set for the warning threshold.
iam having a table A where column ABC is a varchar field with varchar2(50).
currently data stored is like this.
02-27-2009 11:01:33
02-27-2009 11:01:46
03-06-2009 09:07:18
now i want UTC Date time should be in format 2010-02-24T17:08:09Z. in the above format.
converting text values that I have to date and time format (without the millisecs and AM/PM values).
The following is an example:
04-DEC-12 07.47.58.000 AM
or would i need to change this in excel before importing this type of data.
I am running following statement
select TO_DATE( TO_CHAR(CAST((B.EOAWDTTM_MODIFIED) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') from PS_SR_EX_ER_AW_VW B where rownum < 5
and It gives me the error
ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string"
Our data centers resides in different zones and we would like to store the date and time information in GMT format and it should aware of daylight savings.
Which data type is preferred ? Date or timestamp with timezone or timestamp with local time zone. Do we need to configure any parameter on the database?
I would like to do a select to get the current date&time in this format:
2011-10-04T04:20:35.2313103+01:00
do you know how can I do that?
I want to convert this sql statement in this format.
i have data in date format for in time actually it should be 9:30 am and out time at 2:30 pm
SELECT MIN(IN_TIME) AS IN_TIME,MAX(OUT_TIME) AS OUT_TIME FROM EMP_ATND_DETAIL AS ET, EMP_ATND_INFO AS EAI
WHERE ET.EMP_ATND_INFO_ID=EAI.EMP_ATND_INFO_ID AND ATND_DATE = '2006-10-25' AND EMP_ID =24
+---------------------+---------------------+
| IN_TIME | OUT_TIME |
+---------------------+---------------------+
| 2006-10-20 09:30:00 | 2006-10-13 01:30:00 |
| 2006-10-24 02:30:00 | 2006-10-13 06:30:00 |
| 2006-10-24 09:30:00 | 2006-10-13 01:30:00 |
+---------------------+---------------------+
i have should get 9:30 am because that is lessser then 2:30 pm
do i have
SELECT name, emp_id,
min(to_char(IN_TIME, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(OUT_TIME, 'DD/MM/YY HH24:MI')) out_time,
FROM EMP_ATND_DETAIL
How we know a user account locked time in HH:MM:SS format ?
View 1 Replies View Relatedhow to get data from a Table in Date and Time format? Curently I'm using To_TIMESTAMP for a date range.
Here is the code Snippet -
Select *
FROM TABLE_Name
WHERE COUNTRY= 'IN'
AND CALC_DATETIME between TO_TIMESTAMP('2013/06/05', 'YYYY/MM/DD HH:MI:SS') AND TO_DATE ('2013/07/08', 'YYYY/MM/DD HH:MI:SS')
when I select I have to using AND creation_dt>='17-JUL-12' is there a way to change my select to creation_dt='17-JUL-12' ?
on meantime my local format is 18-JUL-12(18-JUL-12) is thee anything I can do?
I am not able to find any information regarding inserting data with timestamp format without putting the date.
This is what i want to do:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';
I create the column data type as timestamp with time zone. This is value i have inserted:
Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);
And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:
Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;
But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.
BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:
Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;
Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?
I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:
DECLARE
v_latest_close DATE;
BEGIN
v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);
END;
the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM
I wish to query the pro_vitual_bytes by hourly average by server name from 04/01/2010 to 04/02/2010. how i going to perform the query.
SERVER_NAME DATE_TIME PRO_VITUAL_BYTES
MLYORABA04/01/2010 00:00:402,238,312,448.00
MLYORABA04/01/2010 00:01:402,241,458,176.00
MLYORABA04/01/2010 00:02:402,238,312,448.00
MLYORABA04/01/2010 00:03:402,241,458,176.00
[Code]....
How To Calculate Average in Forms 6i for example a summary column named (Amount = 5000) and i want to calculate 15% average of this amount i want to calculate it like summary column .
View 2 Replies View RelatedVersion : 11g
I have a table with the following format and data
Serial no exp_Date exp_type exp_amount
1 01-nov-2012 Rent 10000
2 02-nov-2012 gas 250
3 02-nov-2012 insurance 9500
.
.
.
I want to create a sql output for a yearly view in the format
exp_type JAN FEB MAR APR ..... NOV DEC TOTAL AVERAGE PROJECTED
Rent 10000 10000 10000 10000 10000 10000 120000 10000 120000
gas 250 250 250 250 250 250 3000 250 3000
.
.
Now a couple of things in this
1. the average gives the average for the year, so lets say its start of 2013 and we are in feb, there will not be any values for the remaining months, so it should do the average for that exp_type for Jan and Feb based on the exp_amount entered against that type and show what is the expected average. Similary, projected will that average amount and mulitply it by 12 to show the exp amount expected based on current expenses
I was able to come up with the following sql to get the sum based on months, was not sure about average, total and projected
SELECT exp_type
, SUM (CASE WHEN to_char(exp_date,'Mon') = 'Jan' THEN exp_amt END) AS Jan
, SUM (CASE WHEN to_char(exp_date,'Mon') = 'Feb' THEN exp_amt END) AS feb
, SUM (CASE WHEN to_char(exp_date,'Mon') = 'Mar' THEN exp_amt END) AS Mar
, SUM (CASE WHEN to_char(exp_date,'Mon') = 'Apr' THEN exp_amt END) AS Apr
, SUM (CASE WHEN to_char(exp_date,'Mon') = 'May' THEN exp_amt END) AS May
[Code]....
getting the correct avg, total and projected fields also in the same sql?
,I've some database in 11.2 RAC on AIX.I was analyzing the root causes of eviction.Looking AWR Report before the reboot I see:
DB1 Host CPU (CPUs: 6 Cores: 3 Sockets: )~~~~~~~~ Load Average Begin End %User %System %WIO %Idle --------- --------- --------- --------- --------- --------- 4.18 12.33 60.9 12.6 1.6 26.5 Instance CPU~~~~~~~~~~~~ % of total CPU for Instance: 27.4 % of busy CPU for Instance: 37.3 %DB time waiting for CPU - Resource Mgr: 10.6 DB2 Host CPU (CPUs: 6 Cores: 3 Sockets: )~~~~~~~~ Load Average Begin End %User %System %WIO %Idle --------- --------- --------- --------- --------- --------- 3.77 13.93 60.7 12.5 1.6 26.7 Instance CPU~~~~~~~~~~~~ % of total CPU for Instance: 6.9 % of busy CPU for Instance: 9.5 %DB time waiting for CPU - Resource Mgr: 0.0
Do you think these value ar high? This is vmstats at the time of reboot:
DATARUNBCKAVMFREPREPPIPPOPFRPSRPCYFINFSYFCSCUSCSYCIDCWA07/21/2013 00:08:173107.400.345579.92308100003.292187.01019.56084160007/21/2013 00:08:171717.390.187589.884017600003.681169.99421.48281190007/21/2013 00:08:172717.402.121577.816011500003.150157.21018.50384160007/21/2013
[code]...
I'm trying to build a query capture the below information. Table looks something like below .
RoomSubjectStudentsA1Science10A1Maths20B1Science15B2English25C1Deutsch20A1French15C1English10
How can i write a query so that i takes the average of students in each room and display the output? I need to write a query so that it also picks up any new rooms and students added. Average is sum of total students in room by number of times each room is specified.
eg: A1 is listed 3 times a the result would be 10+20+15 divided by 3.
A1 B1 B2 C115 15 20 15
I have to procedure that computes number of project, and average working hours of employees where employee id is passed as a parameter to the procedure. If the average working hours is less than 10 then employee's salary remain the same, otherwise check if number of project is less than 4 then 5% of salary, else 10% of salary is added to the salary.
my tables are:
CREATE TABLE employee(
empid number(5),
empname varchar(20),
address varchar(20),
no_of_dependents number(5),
deptno number(5),
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(empid),
CONSTRAINT EMPLOYEE_FKEY FOREIGN KEY(deptno) REFERENCES department(deptno));
[code]...
the problem is i have to use only 2 cursors - 1 for select and 1 for update.
I want to write a sql qeuery to get result similar to expected average column as shown in sheet.
Record can be uniquely identified by columns ID, PRODUCT and OFFICE. I want to calculate average for each date considering the same record does not exists earlier i.e. I want to consider the latest vote while calculating average. E.g.
Average till date 1/1/2011 is Avg (2, 4, 3) = 3 (as no repeating vote value exists)
Average till date 2/1/2011 is Avg (4, 3, 5, 3) = 3.75 (excluding vote value 2 as 122_UK_LONDON was provided his vote earlier, so considered latest vote value i.e. 5)
Average till date 3/1/2011 is Avg (3, 5, 3, 6, 5, 8 ) = 5 (excluding vote value 2 for 122_UK_LONDON and vote value 4 for 967_Europe_London)
I need to calcaulate the salary avarage for three days prior, leaving the current row. That should happen to every row moving back words.I have given all the details.
create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
name varchar(20),
Start_Date DATE,
Salary Number(8,2),
mv_avg number(8,2)
[code]....
I have the following query that creates sums by week.
SELECT cidterr.rnam,
cidterr.rnum,
cidterr.tnam,
cidterr.tnum,
[code]...
I want to convert this query to just return a single line for -cidterr.rnam, cidterr.rnum, cidterr.tnam, cidterr.tnum
With an average sum by week. Similar to how if I did a sum by week from the original query and placed the results into an excel pivot and said show total as average.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for HPUX: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
I have a problem related with hierarchical queries, I have this tree.
- a
/
b c
/ |
d e fI need to calculate an average per node but using always the child's results for example: Only leafs will have values:
d = 10
e = 20
f = 30
c = 40So the expected result is:
d = 10
e = 20
f = 30
c = 40
b = (10(d) + 20(e) + 30(f)) / 3 (number of child nodes) = 20
a = (20(b) + 40(c)) / 2 (number of child nodes) = 30.
I have tried with recursive queries, hierarchical queries, I guess it's possible with model too but I can't produce the exact results that I need. Maybe in fact there is a very simple solution but I cannot figure it.
Here is an auxiliary WITH that you can use to start your tests:
WITH tree AS
(
SELECT 'd' child, 'b' parent FROM dual UNION ALL
SELECT 'e' child, 'b' parent FROM dual UNION ALL
SELECT 'f' child, 'b' parent FROM dual UNION ALL
SELECT 'b' child, 'a' parent FROM dual UNION ALL
SELECT 'c' child, 'a' parent FROM dual UNION ALL
SELECT 'a' child, null parent FROM dual
[code]....
I've got values for each workday of month in table. Chief said we don't load values of weekend because of space and time economy. Weekend value is copy of last workday. How do I count average for month in this case? Technical task in Excel worksheet consist of each day of month, so average value is 14 689 262,86. I should get the same result in my query. My example below shows values, 0 is workday (present in table), 1 is weekend (absent in table)..
select 12230427, 0 from dual
union all
select 11960157, 0 from dual
union all
select 12965902, 0 from dual
union all
select 13939736, 0 from dual
[Code]...
I want to read a record that has 6 columns
RECORD |Col1 |Col2 |Col3 |Col4 |Average |Standard Deviation
0001____|Null_|5___|8___|10__|8.75___|2.986079
With SQL I want to calculate Average and Standard Deviation.
My problem is only on Some Saturday's my Oracle server's Load average goes high (more than 300 ).
-- oracle version 11.2.0.1.0
-- runs on Sun solaris 10
-- Sun fire V 440
-- Sun storEdge 3315 connected to the server.
Same setup is working find with higher volumes without any problem but only on saturday's, that too not on all saturdays, some specific saturdays the load average goes high.
At the time nothing will be processed from the application side and the cpu utilisation goes high upto 95 %.
I am sending necessary information as follows.
vmstat 6 10
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s3 s4 in sy cs us sy id
3 0 0 28660024 6513408 285 212 2044 2 2 0 0 0 17 0 42 830 3469 1380 22 5 74
125 0 0 29027480 6156256 2 6 34 0 0 0 0 0 5 0 10 791 53770 30278 83 17 0
125 0 0 29027680 6157496 29 140 21 1 1 0 0 0 5 0 9 786 52756 30309 83 17 0
116 0 0 29031600 6159896 24 125 0 0 0 0 0 7 3 0 5 819 54081 31069 83 17 0
[code]....
I´m monitoring a 11g database with OEM. I have a couple of questions regarding the Average Active Sessions chart:
- What does the line 'CPU core' means? The DB is running in a virtual server with 8 CPUs. However, the line 'CPU core' is in 1. Does it mean that Oracle is just using 1 CPU?
- What represents the '99th percentile' line? The chart shows several sessions above that line, something is not working well, isn't it?
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
PL/SQL Extract:
CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......
how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.
i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..