SQL & PL/SQL :: Query To Find The Time Difference

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


ADVERTISEMENT

SQL & PL/SQL :: How To Find The Query Which Is Taking Most Time

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

SQL & PL/SQL :: To Find Time At Which Query Fired By Any Of Users In Database

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

SQL & PL/SQL :: How To Find Time Offset For Given Time

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

SQL & PL/SQL :: Find Difference In Two Schemas?

Feb 25, 2013

I want to find difference between the objects(tables(columns,constraints), views, sequences) in two schemas. One schema is staging and one is development . In development lot of changes are made , Is there a script or a opensource tool which will bring out the difference in columns, constraints in each table and sequences , views etc.

View 2 Replies View Related

SQL & PL/SQL :: How To Calculate Time Difference

Aug 10, 2003

I want to calculate time difference b/w two time clocks, just like we calculate the date difference and answer is in days, In the same way i like to have answer in hrs,min and ss.

View 4 Replies View Related

PL/SQL :: How To Get Time Difference Between Two Dates

Oct 3, 2012

i am using this query to get the time difference between two dates.

select to_timestamp('2012-10-03 12:00:00','YYYY-MM-DD HH:mi:ss') - to_timestamp('2012-10-03 11:00:00','YYYY-MM-DD HH:mi:ss') as diff from dual;

but not getting the correct result.

View 26 Replies View Related

PL/SQL :: How To Find Difference Between Timestamp In Two Variables

Aug 1, 2012

how to find the difference between time stamp in two variables.

Say in a java file i am getting start time and end time i am storing it in a string variables now i need to know what is the time difference of them in millisecond

Srting1 of java will give me some thing like
StartTime =2012-08-01 15:14:20.36 +5:30
Srting2 of java will give me some thing like
EndTime =2012-08-01 15:14:21.254 +5:30

I need to take the millisecond difference between them the code i have written in java and connecting oracle database and doing some logic's in java so i need to capture start time and end time of code.!

View 22 Replies View Related

SQL & PL/SQL :: Calculate Time Difference Between 2 Date

Nov 29, 2011

during application migration, i got one table from MS Access, and have situation where two events are splited into 4 columns (start: date1 time1 and stop: dat2 and time2). How to properly calculate duration between these two events, and show it in format: hh:mi ?

CREATE TABLE ACCBTP_DCZASTOJ
(ID NUMBER(11,0),
NASTANAK_KVARA DATE,
VRIJ_NASTKVARA DATE,
VRIJEME_OPRAVKE DATE,
DATUM_OPRAVKE DATE);

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Find Difference Between Two Table With Respect To Column?

Jul 20, 2013

I have two tables tab1 and tab2 with below data Now requirement is to find difference between two table with respect to column "serial_no" which exist only in table tab1 so as per below at serial_no 2 CC4 in tab1 but CC2 in tab2 so this is different how to dump out this data

TAB1
SQL> select * from tab1;

PARENT CHILD ROW_ID SERIAL_NO
-------------------- -------------------- ---------- ----------
P1 CC1 101 1
P1 CC4 104 2
P1 CC5 105 3
P1 CC2 102 4
P1 CC3 103 5

TAB2
SQL> select * from tab2;
PARENT CHILD PREVIOUS_ROWID ROW_ID NEXT_ROWID
-------------------- -------------------- -------------- ---------- ----------
P1 CC1 0 101 102
P1 CC2 101 102 103
P1 CC3 102 103 104
P1 CC4 103 104 105
P1 CC5 104 105 0

create table tab1(parent varchar2(20), child varchar2(20),row_id integer,serial_no integer);

insert into tab1 values('P1','CC1',101,1);
insert into tab1 values('P1','CC4',104,2);
insert into tab1 values('P1','CC5',105,3);
insert into tab1 values('P1','CC2',102,4);
insert into tab1 values('P1','CC3',103,5);

create table tab2(parent varchar2(20), child varchar2(20),previous_rowid integer,row_id integer,next_rowid integer);

insert into tab2 values('P1','CC1',0,101,102);
insert into tab2 values('P1','CC2',101,102,103);
insert into tab2 values('P1','CC3',102,103,104);
insert into tab2 values('P1','CC4',103,104,105);
insert into tab2 values('P1','CC5',104,105,0);

View 8 Replies View Related

SQL & PL/SQL :: Find Out Date Difference In Day-month-year?

Jul 8, 2010

i want to find out difference between two dates in day-month-year. How can i do it?

For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'

View 4 Replies View Related

Manually Find Difference In Days And Multiply By 24

Apr 25, 2011

1) First Problem:

Difference in hours is supposed to be 35 but it comes as 34.When i manually find the difference in days and multiply by 24 it comes as 34.999999999 and floor converts it as 34.. But when i execute the difference between dates alone and convert to 24 it shows as 35. So when i apply floor to this, it should be 35 instead of 34 right?

SQL> select (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss'))
3 from dual
4 ;
[code].....

1) Second Problem:

For the following query, difference of the dates in minutes is 2100.. so mod(2100,60) should give 0 but it's giving 60.. Separately if if put mod(2100,60) it is giving as 0 only.

SQL> SELECT MOD(((to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440),
3 60) AS duration,
4 (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
5 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440 as diffinmin
[code]....

View 2 Replies View Related

Forms :: Calculate Difference Time Between Two Fields

Mar 24, 2011

I have two different date in my payroll software,

1-Shift_date shift date *used to contain shift timings
2-Attendace_datedate *used to contain employee IN timings

As you all know that shift is a setup form, where user input data once in the starting of software so the shift_date can be "01/jan/2011 16:00 pm" but attendance loads daily and attendance field data can be in this form "24/mar/2011 16:15 pm"..Now I want to calculate difference time between these two fields therefore I used this statement

SQL> Select to_char(attendance_date,'HH24:MI') to_char(shift_date,'HH24:MI') from dual;

but it is showing error: ORA-01722: invalid number...I used hours/minutes format mask in my query because you can see there is a difference of dates between these fields and it will be increase in the coming future and I need late hours and minutes.

View 2 Replies View Related

SQL & PL/SQL :: Calculate Difference Between Multiple Dates At Same Time?

Jan 10, 2012

how to calculate the difference between multiple dates at the same time..

Select to_date('10/10/2011','mm/dd/yyyy')
- to_date('09/10/2011','mm/dd/yyyy')
- to_date('08/10/2011','mm/dd/yyyy') from dual;

Giving me an error...

ORA 00932 : inconsisten data types:expected DATE Julkian got DATE..

View 8 Replies View Related

SQL & PL/SQL :: Get Difference And Convert Integer To Time In Hours And Minutes

Jun 14, 2012

How to get Time Difference between two DateTime Columns in Oracle 10g ?

View 10 Replies View Related

SQL & PL/SQL :: Get End Time By Query Between Start Time And Duration

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

Server Administration :: Find Out Difference Of Data For Some Tables Between Current And Previous Day

May 26, 2011

We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.

View 5 Replies View Related

SQL & PL/SQL :: How To Find Out Time Variance Dynamically

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

SQL & PL/SQL :: Find Lock On Particular Date And Time?

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

SQL & PL/SQL :: How To Find Date And Time Of A Column In Table

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

SQL & PL/SQL :: Find The Time Of A Record Inserted Into Table

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

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 View Related

SQL & PL/SQL :: Date Difference In YY MM Day HH:MI:SS Format Through Query

Feb 21, 2011

I just want to calculate difference between two dates in YY MM DD HH:MI:SS format through a SQL Query (not function).

Sample data is as follows-

to_timestamp('11-Feb-2008 12:23:00','DD-MON-YYYY HH24:MI:SS')
to_timestamp('2-Dec-2010 04:23:22','DD-MON-YYYY HH24:MI:SS')

or

how to calculate age in YY MM Day HH:MI:SS format. Suppose my DOB is '2-Feb-1988 11:10:15 AM'

View 4 Replies View Related

Server Utilities :: How To Find Estimated Time To Complete Import (imp)

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

Performance Tuning :: How To Find Out Queries That Are Executed In Particular Range Of Time

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

Find Queries Bases On Elapsed Time From Window Of 3 Months

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

PL/SQL :: Oracle Dictionary View 2 / Find Queries Run And Its Execution Time

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

How To Find Time Taken For Parsing / Creating Plan And Actual Data Retrieval

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

How To Find Time (timestamp) When Data File Status Changed To Recover

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

SQL & PL/SQL :: How To Find The Count In The Query

Apr 16, 2010

from the below string i want to find the count of the "<-" using a single query.

string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"

If not the query, pointer on using SQL functions.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved