Getting the difference between Dates

To get the difference between  two dates;
The below function return the interval time between two dates:

create or replace function 

get_elapsed (pstart in date , pend in date )

 return interval day to second
as
begin
    return (cast(pend as timestamp)- cast(pstart as timestamp))  DAY TO SECOND;
end ;

SELECT GET_ELAPSED(

to_date('19/11/2014 12:10:20', 'DD/MM/YYYY HH24:MI:SS'), to_date('19/11/2014 13:10:20', 'DD/MM/YYYY HH24:MI:SS') 

)  ELAPSED

 FROM DUAL;

ELAPSED:

+000000000 01:00:00

We can extract hour :

SELECT EXTRACT(hour from GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'))) HOUR
  FROM DUAL;

HOUR:

1

We can extract hours,minutes and seconds 

SELECT
   GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS') ) ELAPSED,
 
   EXTRACT(HOUR FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) HOUR,
             EXTRACT(MINUTE FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) MINUTE,      
           EXTRACT(SECOND FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) SECOND                                          
  FROM DUAL;

    ELAPSED                 HOUR MINUTE SECOND

+000000000 00:49:40 0 49 40

You can refer to Database SQL Language Reference to get more details about Interval type  and extract function; 

A Database Limits

• Datatype Limits 
• Physical Database Limits 
• Logical Database Limits 
• Process and Runtime Limits

Coskan Gundogar = Plan stability through Upgrade to 11G-Why is my plan changed?-Auto Adjusted Dynamic Sampling

Plan stability through Upgrade to 11G-Why is my plan changed?-Auto Adjusted Dynamic Sampling

SQL Patch: Another way to change the plan without changing the code

SQL Patch: Another way to change the plan without changing the code

Indexes: Structure, Splits and Free Space Management Internals from Christian Antognini (OOW14)

Indexes: Structure, Splits and Free Space Management Internals from Christian Antognini

Adaptive Query Optimization from Christian Antognini (OOW14)

Adaptive Query Optimization from Christian Antognini

Everything That Is Really Useful in Oracle Database 12c for Application Developers (OOW 2014, CON 2394

Everything That Is Really Useful in Oracle Database 12c for Application Developers (OOW 2014, CON 2394) from Lucas Jellema

PERL = DBI::db = disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting)

1. $dbh = DBI->connect("DBI:ODBC:$dsn",$user,$password)
2. my $statement=$dbh->prepare(q{select count(*) Total from tab;});
3. $statement->execute() or  die "executing: ", $dbh->errstr;
4. my $rowq = $statement->fetchrow_arrayref->[0];
5.  
6. $dbh->disconnect;

DBI::db=HASH(0x1d56afc)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnectin
g) at chb.pl line 6.


to avoid this you should add  $statement->finish();

1. $dbh = DBI->connect("DBI:ODBC:$dsn",$user,$password)
2. my $statement=$dbh->prepare(q{select count(*) Total from tab;});
3. $statement->execute() or  die "executing: ", $dbh->errstr;
4. my $rowq = $statement->fetchrow_arrayref->[0];
5.  
6. $statement->finish();
7. $dbh->disconnect;

Maximizing Database Performance: Performance Tuning with DB Time

DB-Engines Ranking : The 10 Most Popular DB Engines

Read more about the method of calculating the scores and trend popularity: http://db-engines.com/en/