SQL & PL/SQL :: Evaluating Periods Of Time
Dec 8, 2010
All my tables are part of an Oracle 9i installation.
My table (of pain ) is like is:
USR_ID STATUS CDTS
12345 L_ON 20100101023459WD
12345 L_OFF 20100101082356WD
44653 L_ON 20100305123459WD
44653 L_OFF 20100305182356WD
12345 L_ON 20100305142422WD
12345 L_OFF 20100305171245WD
As you can see it contains data of users logging in and out. Between those logons and logoffs they are active / online. The data is from 2008 til today. Mostly there are many users online same time.
My questions / problems are:
- maximum users online same time (per day / week / month / year)
- time (hh:mm) of peaks
I don't ask you to give me a perfect / ready solution including all code - I just have no clue how to create the queries. To be honest I just have no clue how to start.
View 4 Replies
ADVERTISEMENT
Nov 26, 2010
I have a table like this:
ID1 ID2 Ini_date End_date
1 1 2008-05-14 2010-09-16
1 2 2010-01-21 2010-08-26
..... ..... ............. ...................
and I would like to have a row for each year between ini_date and end_date.
ID1 ID2 YEAR
1 1 2008
1 1 2009
1 1 2010
1 2 2010
View 2 Replies
View Related
Feb 11, 2011
I have a table having a code column A and a date column D1 which represents the days when the code was inserted. I can have more then 1 date for a code.
The idea is to create another column (an id) which will correspond to all the inserted days of a code for a period of 4 month. if, after 4 month of the first insert, another records appears, then another id will be generated for my code. If a code has the date included in one of the intervals existing already, then it will just receive the corresponding id.
Example:
id code date
id1 cd1 01/01/2010
id1 cd1 04/03/2010
id1 cd1 22/04/2010
id2 cd1 27/05/2010
id2 cd1 21/06/2010
If a cd1 appears now on 22/05/2010, then i have to give it id2.
View 3 Replies
View Related
Jun 1, 2010
I'm trying to work out how to take a table like this:
IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09
And display the data like this:
IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
View 13 Replies
View Related
Jun 4, 2012
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].......
View 3 Replies
View Related
Jul 25, 2013
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..
View 2 Replies
View Related
Dec 27, 2010
I have one inline view query which shows exec\ fetch : 2 sec\ 19 sec It gives 500 rows as final out put, when i give rownum<100 it shows exec\ fetch : 1 sec\ 000 sec, and i cannot use this rownum< 100 alternative as this is inline subquery of big query.
What does this exec and Fetch time is?
How to improve fetch time, (esp with sub-query) ?
View 2 Replies
View Related
Jun 16, 2013
I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code
""SELECT TO_CHAR
(TRUNC (SYSDATE)
+ (TO_DATE (:T_DONATION_END_TIME, 'HH24MI') - TO_DATE (:T_DONATION_START_TIME, 'HH24MI')),
'HH24MI')
INTO :T_DONATION_DURATION
[code].......
View 5 Replies
View Related
Jan 31, 2013
I have a table which stores apointment start times and appointment end times. For the sake of this thread I will call them appt.start_time and appt.end_time. I then have a check in time and a check out time for the customer. The only thing is they ONLY way to distinguish between a check in time and a check out time is which one has the earlier time and which one has the later time. Obviously the earlier time will be the check and the later time will be the check out.
This is fine, however sometimes they may forget to check a person in or out and I need to determine whether the time should be insert into the check_in column or the check_out column. To do this I was thinking of comparing the time with the appointment start and end time and if it was closer to the appointment start time put it into the check_in column and if its closer to the appointment end time put it into the check_out column. But I was wondering how I would go about doing this.
The time I will want to compare against the appointment start and end time I will store in a variable called v_time and have this as part of my query, im just unsure of what way to write the query so as to check if the time is closer to the start or end time.
View 5 Replies
View Related
Jun 7, 2010
. I have this query:
select asl1.agentsessionid, asl1.endtime, asl2.starttime, 127 as agentstatus
from
(
select asl1.agentsessionid as sessionid1, min(asl2.agentsessionid) as sessionid2
from cti.agentsessionlog asl1
[code]...
As you can see from my where statement I want to compare the endtime with the startime. This query returns zero results. Is there a way to write the where statement different so I can have results?
View 6 Replies
View Related
Sep 23, 2010
My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?
View 1 Replies
View Related
Jul 4, 2011
I have an application in which time is show as . But, in the table in Oracle it is showing as 13:00. The application is taking the time from OS. OS time is 18:30 IST. Time in SYSDATE is also showing as 18:30. DBTIMEZONE is '+5:30'. what is the problem in Database and how that can be rectified to show the time as same as OS time.
View 4 Replies
View Related
Oct 2, 2012
We have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.
Service Time : 15.36%
Wait Time : 84.64%
This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.
As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.
View 17 Replies
View Related
Apr 7, 2011
I'm using Oracle 11g and I have a bunch of indexes and I want to check if they are being used. I just ran my workloads and now I want to see when each one was last used so I can see if it was during my timeframe or not.
After I ran my test, I found the below, but since I did not enable this, plus I have many indexes.
--Monitor an index to see if it's used
alter index SAMPLE_INDEX monitoring usage;
select * from v$object_usage where index_name = 'SAMPLE_INDEX';
alter index SAMPLE_INDEX nomonitoring usage;
View 4 Replies
View Related
Jan 24, 2012
I have a field " Tran_date with data type Date . This field contains date as well as time both . while I run this query :
select to_char(tran_date,'DD-MON-YYYY HH24:MI:SS') from ABC
WHERE lOC='1' ;
It is showing result :
21-JAN-2012 00:00:00
Why it is showing 00:00:00 although there is time like 08:25:12 Where is the problem ? Why I can'y see time . I need to change anything ,anywhere ?
View 9 Replies
View Related
Jun 17, 2010
I have an issue while converting Date. I need to compare the Time Captured while comparing Dates,
find the following scenario,
SELECT TO_CHAR(ffdh.last_update_datetime,'DD-MON-RR-HH24:MI:SS') TIME_STAMP ,
ffdh.last_update_datetime,
to_date(ffdh.last_update_datetime,'DD-MON-RR-HH24:MI:SS') DATES ,
ffdh.fiscal_doc_id INVOICE_ID
[Code]....
Note That ffdh.last_update_datetime is DATE, and the Parameter for the value '17-JUN-10-01:30:17' is VARCHAR2
I suspect when I am doing to_date above both are converting only to Date and not having Time.
I checked the NLS_DATE_FORMAT as following,
SQL> show parameter nls_date_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-RR
Do I need to Change the NLS_DATE_FORMAT ?
View 5 Replies
View Related
Sep 27, 2011
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?
View 5 Replies
View Related
Feb 21, 2011
I have one table with 10 rows and its frequently updated now my question is that, how can it get the latest date of updation of that rows?
View 4 Replies
View Related
Oct 12, 2012
i am using one stored procedure there in one variable value is coming like this: Jan 1 1900 6:00AM
from this i want time how to get the time from that.
View 3 Replies
View Related
Mar 6, 2012
Any way in sql to get the data from my local machine, where only client is installed, not the database.
View 4 Replies
View Related
Jun 30, 2010
Checkout the following code
CREATE TABLE CHK(dt_request_datetime DATE);
INSERT INTO CHK VALUES(TO_DATE('25-JUN-10 04.12.57 AM',
'DD-MON-YY HH:MI:SS AM'));
COMMIT;
[Code].....
TZ_OFFS TZ_OFFS GMT_TIME
------- -------
-04:00 +00:00 25-JUN-10 04.12.57.000000000 AM
EST_TIME
-----------
25-JUN-10 12.12.57.000000000 AM EST
9 am
----
25-JUN-10 09.00.00.000000000 AM EST
8 pm
-----
25-JUN-10 08.00.00.000000000 PM EST
OUTPUT
---------
30-JUN-11
My doubt here is that even though dt_request_datetime is between 9 am in EST timezone and 8 pm in EST timezone the query output is not according to that.
View 2 Replies
View Related
Sep 14, 2011
Is is possible to get the time last DML was ran on a table.
My aim is to get the list of tables which were not modified(Select,insert,update,delete) within last one year.
DB version details:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
View 1 Replies
View Related
Oct 4, 2012
i was using sql server there is time keyword but in oracle which keyword i should take instead of time keyword?
View 7 Replies
View Related
Aug 28, 2012
I have the following query
Select ei.emp_code,
TO_CHAR(ei.intime, 'dd/mm/rr') "IN Date" ,
--TO_CHAR(ei.intime, 'hh24:mi:ss') IN_Time,
MIN(TO_CHAR(ei.intime, 'hh24:mi:ss')) IN_Time,
TO_CHAR(ei.intime, 'FMDAY') IN_DAY
[Code].....
no I want to modify this query, I mean I want to get data between time 09:15 to 09:12 In time.
View 3 Replies
View Related
Oct 17, 2012
query to identify the time from midnight.
For ex. 3/06/2012 2:29:24 AM means 2:29 from midnight of 3/06/2012 and 21:31 from midnight of 4/06/2012
View 4 Replies
View Related
Sep 11, 2012
With utl_http you can set the timeout .How can i set a time out with use of utl_dbws ?
View 3 Replies
View Related
May 2, 2013
We are using Oracle 10g on Linux. Followings are some contents from the AWR report:
Elapsed: 60.26 (mins)
DB Time: 437.27 (mins)Here why the DBTime is greater than Elapsed Time. Is it due to the fact that this is the total time spent in user calls by multiple sessions i.e. ( some sessions were on CPU, some were in IDLE state waiting for I/O)
The below statistics show that CPU was used only for 5,363 seconds. We have 8 CPU grid, so total CPU time is 60.26* 8= 482.08 Min. I want to know where does the remaining time was spent. Also there is 20.4% of Total Call Time. How does it is calculated.
CPU time 5,363 20.4. The below statistics for reference:
BUSY_TIME 1,218,675
IDLE_TIME 1,675,112
[code].....
View 2 Replies
View Related
Jun 17, 2013
I have two methods using pro*c to execute a query SQL and read the results. The query is a "select from" a view, this view is a union between two tables. The first method creates a cursor and fetches the results line by line. A second method creates a cursor and fetches the results in parts.
Method 1)
CODEEXEC SQL FETCH Cursor1 INTO ...
Method 2)
CODEEXEC SQL FOR :numLines FETCH Cursor1 USING DESCRIPTOR areaDescSQL;
I had executed this methods in my development enviroment, using Oracle 11g, Suse linux. As I expected, the method 2 spent much less time than the method 1. But, in the "customer enviroment", using another database, Oracle 11g and HP-UX, these methods spent almost the same time.
Is there some Oracle's configuration or parameter's settings that maybe explain that? What configuration can be differente between the two databases? What can I do to improve the time spent?
View 1 Replies
View Related
Apr 24, 2005
I only know to retrieve data (date & time) by the following statement : "select sysdate from ..."
but now I want to make the fields current date and current time separately and put in a adult file with these two fields. how to do it.
View 11 Replies
View Related
May 5, 2011
In our production environment some SP's are executing longer duration, but when same SP is executed from PLSQL Developer client it is executing vary quickly.
View 3 Replies
View Related
