PL/SQL :: How To Check If Time Is Closer To Start Or End Time
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.
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 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?
Version: 11.2 I am not an SQL expert. I am trying to subtract the start time and end time and thereby find the difference between these two times. In the below example , the difference between start_time and end_time is around 52 minutes (6:15 PM to 7:07 PM)I tried some stuff to find the difference below. But it didn't work out .
SQL> create table t (start_time date, end_time date); Table created. SQL> desc t Name Null? Type ----------------------------------------------------------------- -------- --------------------- START_TIME DATE END_TIME DATE SQL> insert into t values (to_date('24/JUN/2013 18:15:42', 'DD/MON/YYYY HH24:MI:SS'), to_date('24/JUN/2013 19:07:54','DD/MON/YYYY HH24:MI:SS')); 1 row created. SQL> commit; Commit complete. SQL>SQL>SQL> select * from t; START_TIME END_TIME-------------------- --------------------24 Jun 2013 18:15:42
formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.
I have a simple query which will return either A or B depending on the projected oven out date and time. If the projected oven out date and time is between 6am and 6pm, A should be returned. Otherwise B if time is between 6pm and 6am of the next day. My problem is that I do not know how to display A or B depending on the projected oven out date and time. I am using the query below to get the projected oven out date and time.
SELECT to_char((ti.txndate + pm.baketime/24),'MM/DD/YYYY HH:MI:SS PM') FCSTDOvenOut FROM CONTAINER c
I have a stored procedure running in which there is a cursor which fetches around 1500000 records and then query another table using the fetched record values.
I cannot modify the procedure as its on production. I want to know which cursor record is currently being processed by the procedure, and how many are still remaining ? How to check the cursor stats at runtime. I want to check up to which record the cursor has been fetched and how many are still remaining. I have cursor name. Is there some dynamic view to check cursor stats at runtime ?
Need to check the elapse time for particular query ran from last 24 hours , it was ran multiple times and need to know for each execution what is elapse time .
I have just migrated database to 11.2 ..Migration is successfull and now database is in open mode working fine.BUT i m getting following mesage in alert log file
"Time drift detected. Please check VKTM trace file for more details."I m using windows platform.
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].......
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.
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?
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.
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.
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;
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 )
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.
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
