PL/SQL :: Find Duration By Subtracting Start And End Time?
Jun 25, 2013
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
[Code] .......
View 5 Replies
ADVERTISEMENT
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
Aug 5, 2013
find the Execution time frequency of the database jobs?i need the Execution time like this..
Job A is Running on every monday 10:30 AM OR
Job A is Running on every MONTH OR
Job A is Running on every DAY 10:30 AM
View 4 Replies
View Related
Jun 20, 2010
Is there any way to find the last run duration of a job which was scheduled using DBMS_JOB?
View 3 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
Feb 5, 2011
I am creating report for this i want to make one query..
Query like this
My input is YEAR, MONTH AND WEEK and i want to find out start date and end date for this week...
Scenario like this
-----------------
I/P - Year = 2011, WEEK = 2 , Month - FEB
i will get o/p like this '06-feb-2010' and '12-feb-2010'...
View 10 Replies
View Related
Feb 6, 2013
I need to go on day start and end time for each day. Any function that will go on start and end time according to sysdate for that day.
View 7 Replies
View Related
Mar 28, 2013
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.
create table rel_data_mf (header_data varchar2(10),start_time varchar2(100),end_time varchar2(100),grade varchar2(1));
--table rel_data_mf data as comma separated values
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
ENG,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U
[Code]...
--Required output
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
ENG,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,A
ENG,2013-03-29 07:00:00-05:00,2013-03-29 10:00:00-05:00,U
MATH,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
MATH,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,B
MATH,2013-03-29 07:00:00-05:00,2013-03-29 13:00:00-05:00,U
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
Sep 26, 2013
I have a peculiar situation where i have a column which has a string. The text has to be parsed to identify two date strings.
After converting the strings to date they have to be subtracted to obtain the time taken.
Table to create
CREATE TABLE JOB_EXECUTION
(
RUN_DATE DATE,
RUN_ORDER NUMBER,
RUN_DIV VARCHAR2(50),
RUN_TYPE VARCHAR2(50),
RUN_FMT VARCHAR2(50),
[code]....
Insert sample data
INSERT INTO JOB_EXECUTION (RUN_DATE ,RUN_ORDER ,RUN_DIV ,RUN_TYPE ,RUN_FMT ,PICKUP_STATUS ,START_TIME ,END_TIME ,REMARKS) VALUES
(sysdate-1,1,'AE','FILE-HFM','NY_PLAN-2014','FINISHED',TRUNC(SYSDATE),SYSDATE,'"Load Process is INITIATed: with [BATCHID:= FILE-HFM_RUN_ORDER#_5 ]: as of @ 26-Sep-2013 10:26:50.
AFM-Load END PROCESS: as of @ 26-Sep-2013 10:26:52.
AUTO-LOAD RUNNING :as of @ 26-Sep-2013 10:26:57.
FILEs-LOAD PROCESS: as of @ 26-Sep-2013 10:26:59.
ALLOCATIONs PROCESS: as of @ 26-Sep-2013 10:27:52.
[code]....
The select I tried.
WITH reporting_info AS (
SELECT b.run_date,b.run_div,b.run_type,b.run_fmt,1234 as Batch_id, start_time as BATCH_START_TIME, end_time AS BATCH_END_TIME,
REPLACE(substr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')),chr(10))-2 ),'FILEs-LOAD PROCESS: as of @ ','') FILE_LOAD,
REPLACE(substr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')),chr(10))-2 ),'ALLOCATIONs PROCESS: as of @ ','') ALLOCATION,
[code]....
Since the date format does not contain AM or PM in it ... while subtracting time values crossing midnight the value goes in negative .... How to subtract two date strings with out AM or PM values to get a positive value ?
View 5 Replies
View Related
Sep 3, 2010
My problem is that I am working with a balance (Infor/WorkBrain - Time & Attendance) I have a balance called Bank Holiday(bal_id 10044). Every holiday that an employee works, he is allowed to take another day off in lieu of the day he worked. The employee has up to twelve months to take that holiday.
My job is to figure out when he worked that holiday (not a problem) and send an email/note at the 11 month mark and notify the employee and supervisors that said employee has 1 month to use his banked holiday.
Here is what the employee_balance log looks like.
EBLOG_IDEMP_IDBAL_IDEBLOG_DELTAWRKS_WORK_DATEWRKS_IDEBLOG_MESSAGE
51200128537100441 10/15/2010 5281229TCODE_NAME:FR_HWS
51150828537100441 7/1/2010 5136881TCODE_NAME:FR_HWS
51150928537100441 7/27/2010 5136942TCODE_NAME:FR_HWS
51150528537100441 8/1/2010 5137137TCODE_NAME:FR_HWS
5294372853710044-1 8/31/2010 5137173TCODE_NAME:FR_HWO
52947828537100441 6/3/2010 5001014TCODE_NAME:FR_HWS
My problem is the data/constraints I am working with. I can tell what day the holiday was based on a time code (EBLOG_MESSAGE) and EBLOG_DELTA shows a positive 1. I can also tell when the employee has used a bank holiday (EBLOG_DELTA is -1) and EBLOG_MESSAGE = FR_HWO.
But how to I perform the deduction so that I get four most current dates. Meaning since my first date that the employee gets for a holiday wokred is 6/3/2010 but the employee used a bank holiday on 8/31/2010. So I want to display the dates 7/1/2010, 7/27/2010, 8/1/2010 and 10/15/2010.
My preference would be not to add anything new to this table as it is a core table for Infor/WorkBrain.
View 2 Replies
View Related
Oct 21, 2013
I am having a problem when calculating dates in a package usiong Toad. I have my function :
FUNCTION PORTFOLIO_MEMO_RPT_Sql( schema_name IN VARCHAR2 , select_business_date IN DATE ) RETURN VARCHAR2.....SELECT DISTINCT Case when NVL(TO_DATE(''09/24/2013'',''MM/DD/YYYY'') - DECODE(mn.Active, 1, mn.DueDate, sc.DueDate ),0) = 0 then ''0Days'' when NVL(''' || select_business_date || ''' - DECODE(mn.Active, 1, mn.DueDate, sc.DueDate ),0) = 0
then ..... when I give it my variable from outside , it is not working......but when I use a date inside, it works. I call my function from outside like :
SELECT * FROM TABLE ( FI_rpt.Fi_Rpt_Pkg_loan.PORTFOLIO_MEMO_RPT (sys_context('userenv','session_user'),TO_DATE('09/24/2013','MM/DD/YYYY')) )
View 17 Replies
View Related
Nov 5, 2012
Is it possible to generate AWR report for the duration of 5 min? As we know that snapshots are generated for every 1 hour, which we specified in parameter.
By changing the parameter to 2 min, what could be the impact on database?
View 7 Replies
View Related
Jun 5, 2012
I'm trying to install Oracle 10g Enterprise Ed. (on Windows 2003 Std. Ed) and I get some errors:
First “Fail to start OCR” I press "continue" and the installation continue. Next “Operative System Error when start OracleCSService” and when I press "continue" the installation finish.
At the end of installation, It seems like Oracle DB is Ok, but not really...I can't connect to database from Enterprise Manager and the OracleCSService is in "Starting mode".
View 3 Replies
View Related
Mar 28, 2012
I wanted to calculate the time variance dynamically as below.I have one table called process_status where we can see the process name, start time, end time and the status .
1. Now i wants to calculate the duration(end_time - start_time) in hh:mi:ss format and i got the output using below query.
select name, start_time, end_time, status, to_char(trunc(sysdate) + (end_time - start_time), 'HH24:MI:SS') duration from process_status ;
I got currect oupput but i am stuggling with the below step.
2). Actually the process expectaiotn time is 2 hours only(fixed time). Now i wants to calculate variance b/w expected time and the duration time(end_time - start_time)
i.e variance = expected_time - (end_time - start_time ) ;
here expected time is 02:00:00(fixed).
I tried using with to_char and to_date function but no luck.
View 7 Replies
View Related
Jul 4, 2011
I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.
View 2 Replies
View Related
Dec 18, 2012
we can easily find out the locks which are currently active session.
Is any other way to find out locks on particular date and time using data dictionary?
For example, we have to find whether TT_objects occured the lock or not on ON yesterday.
View 3 Replies
View Related
Aug 21, 2010
I need to query a table to get the time difference based on different dates.
SAMPLE DATA:
JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12
in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as
01:23:12-00:56:00 and the difference should be in minutes.
for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.
View 2 Replies
View Related
Jun 20, 2013
How to find date and time of a column in table ?
say for example there is a column called 'date_txn' in a table .When i select that particular column it display output as 'June 2013'.But i want output "with date and time".
View 10 Replies
View Related
Oct 20, 2010
I have a table which is getting populated from a source. I dont have a time-stamp column in the table. Is there anyway to find the time when a record was inserted into the table?
View 3 Replies
View Related
Oct 25, 2010
I have 10 users in my database, I need to find the time at which a query is fired by any of the users in the database. provide the query for this? I think i have to use V$sql,v$sqlarea or v$sql.
View 1 Replies
View Related
Apr 17, 2012
I am using imp / Schema level. Dump file contains 3 schemas with 120 GB size.Imp is running now. How to find completion time. So that I can inform to application developer about activity completion time.
View 4 Replies
View Related
Sep 9, 2011
here we have an scenario where we want to find out all the sql statements that are executed in a particular time. The sql statements are executed via our application. I tried in awr report but it shows only the sql query which has taken long time to execute. and i even tried in V$session and V$sqlarea. how to view the executed sql statements in a particular session/current session
View 3 Replies
View Related
Nov 19, 2012
how to find out queries bases on elapsed time from the window of 3 Months
DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT
View 6 Replies
View Related
Feb 6, 2013
Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.
View 2 Replies
View Related
Jul 13, 2010
Is there any way to find out the division between the time taken for query parsing, creating execution plan and actual data retrieval seperately? If I enable 'set timing on' I see the elapsed time which is the total time taken for all these 3. Some of my queries are taking long time when I run it first time and so want to know what is it taking long? is it the parsing or creating the execution plan, if so what can I optimize.
View 3 Replies
View Related
Sep 14, 2011
Oracle Database Version : 9.2.0.8.0
Some of the datafiles status have been changed to 'RECOVER', because the datafiles are physically missing.
Now, how can i find that when (timestamp) the status of the datafiles have been changed, as i am unable to find when the datafiles have been physically lost?
Please consider both the case :
1) when the database is in ARCHIVELOG Mode.
2) when the database is in NOARCHIVELOG Mode.
View 1 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
