Oracle Tips and Tricks — David Fitzjarrell

February 17, 2016

“That Ain’t Right”

Filed under: General — dfitzjarrell @ 09:46

"Is everyone who lives in Ignorance like you?" asked Milo.
"Much worse," he said longingly. "But I don't live here. I'm from a place very far away called Context." 
-- Norton Juster, The Phantom Tollbooth 

Dates are, well, dates. They are not strings, they are not numbers, they are dates and to handle them properly the correct data type needs to be used. I can’t count how many times I’ve seen dates stored as VARCHAR2 strings or as numbers, neither of which treat them properly or order them correctly. Recently in a forum I frequent this question was asked:


Version Oracle 11 g R2


Hello every body
 

First of All i know the Format is wrong
but as it is a old project i need to extract the report as it is

there is a table with two fields and data as follow

create table Onduty_leave
(
Start_time varchar2(100),
end_time  varchar2(100)
);

insert into onduty_leave (start_time,end_time) values('09.00 am','06.00 pm');

Minutes i am handling differently

Please just tell me how to calculate hours between 09 am to 06 pm 

Yes, this person realizes that the data type is incorrect for the data being stored, but the fact remains someone thought this was a good idea and implemented it into a production system. Of course it’s possible to provide the results the person posting this question is after, but it would be so much easier had the correct data type been used. Be that as it may let’s proceed with the current table definition and show what can be done to calculate the desired quantity. Let’s build and populate the table with some simple code:


SQL> 
SQL> --
SQL> -- Create the table with the improper data type
SQL> --
SQL> create table Onduty_leave
  2  (
  3  Start_time varchar2(100),
  4  end_time  varchar2(100)
  5  );

Table created.

SQL> 
SQL> --
SQL> -- Populate the table
SQL> --
SQL> begin
  2  	     for i in 1..10 loop
  3  		     insert into onduty_leave
  4  		     values(sysdate-i, sysdate+i);
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Set the display format to see the date and time
SQL> --
SQL> alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

Session altered.

SQL> 
SQL> --
SQL> -- Format the strings for display
SQL> --
SQL> column start_time format a25
SQL> column end_time format a25
SQL> 

We are now ready to compute the hours between the two provided dates in each record. Since these are VARCHAR2 strings it will be necessary to use TO_CHAR and TO_DATE to get proper results.


SQL> --
SQL> -- 24 hours per day, find days between and multiply
SQL> -- This uses the entire date string to provide correct
SQL> -- results
SQL> --
SQL> 
SQL> select (to_date(end_time) - to_date(start_time))*24 hours_between
  2  from onduty_leave;

HOURS_BETWEEN                                                                   
-------------                                                                   
           48                                                                   
           96                                                                   
          144                                                                   
          192                                                                   
          240                                                                   
          288                                                                   
          336                                                                   
          384                                                                   
          432                                                                   
          480                                                                   

10 rows selected.

SQL> 

It isn’t rocket science but it is a bit more work than we should be doing to get these answers. The data in the table was generated simply; let’s generate some new data that may more accurately reflect real-world entries into this table:


SQL> --
SQL> -- Purge all current data
SQL> --
SQL> truncate table onduty_leave;

Table truncated.

SQL> 
SQL> --
SQL> -- Populate table with more realistic records
SQL> --
SQL> begin
  2  	     for i in 1..10 loop
  3  		     insert into onduty_leave
  4  		     values(sysdate, sysdate+((25*i)/24));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 

Using just the time from each entry let’s compute the new hours difference between them:


SQL> --
SQL> -- Hours between entries
SQL> -- Use only the time portion of the date
SQL> --
SQL> -- This produces wrong results since the date
SQL> -- isn't included in the calculation
SQL> --
SQL> select start_time, end_time,
  2  	    (to_date(to_char(to_date(end_time), 'HH24:MI:SS'), 'HH24:MI:SS') - to_date(to_char(to_date(start_time), 'HH24:MI:SS'), 'HH24:MI:SS'))*24 hours_between
  3  from onduty_leave;

START_TIME                END_TIME                  HOURS_BETWEEN               
------------------------- ------------------------- -------------               
17-FEB-2016 09:12:28      18-FEB-2016 10:12:28                  1               
17-FEB-2016 09:12:28      19-FEB-2016 11:12:28                  2               
17-FEB-2016 09:12:28      20-FEB-2016 12:12:28                  3               
17-FEB-2016 09:12:28      21-FEB-2016 13:12:28                  4               
17-FEB-2016 09:12:28      22-FEB-2016 14:12:28                  5               
17-FEB-2016 09:12:28      23-FEB-2016 15:12:28                  6               
17-FEB-2016 09:12:28      24-FEB-2016 16:12:28                  7               
17-FEB-2016 09:12:28      25-FEB-2016 17:12:28                  8               
17-FEB-2016 09:12:28      26-FEB-2016 18:12:28                  9               
17-FEB-2016 09:12:28      27-FEB-2016 19:12:28                 10               

10 rows selected.

SQL> 

The initial request was flawed by assuming each record would reference a single day; even though this may be true for the actual data the solution cannot rely on such assumptions. Obviously the results from that last query were wrong; let’s use the full date string and see what values we get:


SQL> --
SQL> -- 24 hours per day, find days between and multiply
SQL> --
SQL> -- Use full date string to compute the hours between
SQL> -- entries
SQL> --
SQL> 
SQL> select start_time, end_time,
  2  	    (to_date(end_time) - to_date(start_time))*24 hours_between
  3  from onduty_leave;

START_TIME                END_TIME                  HOURS_BETWEEN               
------------------------- ------------------------- -------------               
17-FEB-2016 09:12:28      18-FEB-2016 10:12:28                 25               
17-FEB-2016 09:12:28      19-FEB-2016 11:12:28                 50               
17-FEB-2016 09:12:28      20-FEB-2016 12:12:28                 75               
17-FEB-2016 09:12:28      21-FEB-2016 13:12:28                100               
17-FEB-2016 09:12:28      22-FEB-2016 14:12:28                125               
17-FEB-2016 09:12:28      23-FEB-2016 15:12:28                150               
17-FEB-2016 09:12:28      24-FEB-2016 16:12:28                175               
17-FEB-2016 09:12:28      25-FEB-2016 17:12:28                200               
17-FEB-2016 09:12:28      26-FEB-2016 18:12:28                225               
17-FEB-2016 09:12:28      27-FEB-2016 19:12:28                250               

10 rows selected.

SQL> 

This looks much better, but it’s still based on date values stored as strings. With the format specified we can successfully order the data for a single month, but when we add data for other months and sort it ‘strange’ things can happen:


SQL> --
SQL> -- Populate table with more records
SQL> --
SQL> begin
  2  	     for i in 11..100 loop
  3  		     insert into onduty_leave
  4  		     values(sysdate, sysdate+((25*i)/24));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Sort the data
SQL> --
SQL> select *
  2  from onduty_leave
  3  order by 2;

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:04      01-APR-2016 03:29:04                                  
17-FEB-2016 09:29:04      01-MAR-2016 22:29:04                                  
17-FEB-2016 09:29:04      01-MAY-2016 08:29:04                                  
17-FEB-2016 09:29:04      02-APR-2016 04:29:04                                  
17-FEB-2016 09:29:04      02-MAR-2016 23:29:04                                  
17-FEB-2016 09:29:04      02-MAY-2016 09:29:04                                  
17-FEB-2016 09:29:04      03-APR-2016 05:29:04                                  
17-FEB-2016 09:29:04      03-MAY-2016 10:29:04                                  
17-FEB-2016 09:29:04      04-APR-2016 06:29:04                                  
17-FEB-2016 09:29:04      04-MAR-2016 00:29:04                                  
17-FEB-2016 09:29:04      04-MAY-2016 11:29:04                                  
17-FEB-2016 09:29:04      05-APR-2016 07:29:04                                  
17-FEB-2016 09:29:04      05-MAR-2016 01:29:04                                  
17-FEB-2016 09:29:04      05-MAY-2016 12:29:04                                  
17-FEB-2016 09:29:04      06-APR-2016 08:29:04                                  
17-FEB-2016 09:29:04      06-MAR-2016 02:29:04                                  
17-FEB-2016 09:29:04      06-MAY-2016 13:29:04                                  
17-FEB-2016 09:29:04      07-APR-2016 09:29:04                                  
17-FEB-2016 09:29:04      07-MAR-2016 03:29:04                                  
17-FEB-2016 09:29:04      07-MAY-2016 14:29:04                                  
17-FEB-2016 09:29:04      08-APR-2016 10:29:04                                  
17-FEB-2016 09:29:04      08-MAR-2016 04:29:04                                  
...

100 rows selected.

SQL> 

Oracle doesn’t know these are dates, so they are sorted as ASCII text making the order anything but logical, as far as dates go. Let’s drop the orginal table, recreate with the proper date type and try this again:


SQL> --
SQL> -- Drop the original table
SQL> --
SQL> drop table onduty_leave purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Create the table with the proper data type
SQL> --
SQL> create table Onduty_leave
  2  (
  3  Start_time date,
  4  end_time  date
  5  );

Table created.

SQL> 
SQL> --
SQL> -- Populate the table
SQL> --
SQL> begin
  2  	     for i in 1..10 loop
  3  		     insert into onduty_leave
  4  		     values(sysdate, sysdate+((25*i)/24));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Compute the hours between entries for a given record
SQL> --
SQL> select (end_time - start_time)*24 hours_between
  2  from onduty_leave;

HOURS_BETWEEN                                                                   
-------------                                                                   
           25                                                                   
           50                                                                   
           75                                                                   
          100                                                                   
          125                                                                   
          150                                                                   
          175                                                                   
          200                                                                   
          225                                                                   
          250                                                                   

10 rows selected.

SQL> 
SQL> --
SQL> -- Populate table with more records
SQL> --
SQL> begin
  2  	     for i in 11..100 loop
  3  		     insert into onduty_leave
  4  		     values(sysdate, sysdate+((25*i)/24));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Sort the data
SQL> --
SQL> select *
  2  from onduty_leave
  3  order by 2;

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      18-FEB-2016 10:29:05                                  
17-FEB-2016 09:29:05      19-FEB-2016 11:29:05                                  
17-FEB-2016 09:29:05      20-FEB-2016 12:29:05                                  
17-FEB-2016 09:29:05      21-FEB-2016 13:29:05                                  
17-FEB-2016 09:29:05      22-FEB-2016 14:29:05                                  
17-FEB-2016 09:29:05      23-FEB-2016 15:29:05                                  
17-FEB-2016 09:29:05      24-FEB-2016 16:29:05                                  
17-FEB-2016 09:29:05      25-FEB-2016 17:29:05                                  
17-FEB-2016 09:29:05      26-FEB-2016 18:29:05                                  
17-FEB-2016 09:29:05      27-FEB-2016 19:29:05                                  
17-FEB-2016 09:29:05      28-FEB-2016 20:29:05                                  

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      29-FEB-2016 21:29:05                                  
17-FEB-2016 09:29:05      01-MAR-2016 22:29:05                                  
17-FEB-2016 09:29:05      02-MAR-2016 23:29:05                                  
17-FEB-2016 09:29:05      04-MAR-2016 00:29:05                                  
17-FEB-2016 09:29:05      05-MAR-2016 01:29:05                                  
17-FEB-2016 09:29:05      06-MAR-2016 02:29:05                                  
17-FEB-2016 09:29:05      07-MAR-2016 03:29:05                                  
17-FEB-2016 09:29:05      08-MAR-2016 04:29:05                                  
17-FEB-2016 09:29:05      09-MAR-2016 05:29:05                                  
17-FEB-2016 09:29:05      10-MAR-2016 06:29:05                                  
17-FEB-2016 09:29:05      11-MAR-2016 07:29:05                                  

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      12-MAR-2016 08:29:05                                  
17-FEB-2016 09:29:05      13-MAR-2016 09:29:05                                  
17-FEB-2016 09:29:05      14-MAR-2016 10:29:05                                  
17-FEB-2016 09:29:05      15-MAR-2016 11:29:05                                  
17-FEB-2016 09:29:05      16-MAR-2016 12:29:05                                  
17-FEB-2016 09:29:05      17-MAR-2016 13:29:05                                  
17-FEB-2016 09:29:05      18-MAR-2016 14:29:05                                  
17-FEB-2016 09:29:05      19-MAR-2016 15:29:05                                  
17-FEB-2016 09:29:05      20-MAR-2016 16:29:05                                  
17-FEB-2016 09:29:05      21-MAR-2016 17:29:05                                  
17-FEB-2016 09:29:05      22-MAR-2016 18:29:05                                  

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      23-MAR-2016 19:29:05                                  
17-FEB-2016 09:29:05      24-MAR-2016 20:29:05                                  
17-FEB-2016 09:29:05      25-MAR-2016 21:29:05                                  
17-FEB-2016 09:29:05      26-MAR-2016 22:29:05                                  
17-FEB-2016 09:29:05      27-MAR-2016 23:29:05                                  
17-FEB-2016 09:29:05      29-MAR-2016 00:29:05                                  
17-FEB-2016 09:29:05      30-MAR-2016 01:29:05                                  
17-FEB-2016 09:29:05      31-MAR-2016 02:29:05                                  
17-FEB-2016 09:29:05      01-APR-2016 03:29:05                                  
17-FEB-2016 09:29:05      02-APR-2016 04:29:05                                  
17-FEB-2016 09:29:05      03-APR-2016 05:29:05                                  

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      04-APR-2016 06:29:05                                  
17-FEB-2016 09:29:05      05-APR-2016 07:29:05                                  
17-FEB-2016 09:29:05      06-APR-2016 08:29:05                                  
17-FEB-2016 09:29:05      07-APR-2016 09:29:05                                  
17-FEB-2016 09:29:05      08-APR-2016 10:29:05                                  
17-FEB-2016 09:29:05      09-APR-2016 11:29:05                                  
17-FEB-2016 09:29:05      10-APR-2016 12:29:05                                  
17-FEB-2016 09:29:05      11-APR-2016 13:29:05                                  
17-FEB-2016 09:29:05      12-APR-2016 14:29:05                                  
17-FEB-2016 09:29:05      13-APR-2016 15:29:05                                  
17-FEB-2016 09:29:05      14-APR-2016 16:29:05                                  

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      15-APR-2016 17:29:05                                  
17-FEB-2016 09:29:05      16-APR-2016 18:29:05                                  
17-FEB-2016 09:29:05      17-APR-2016 19:29:05                                  
17-FEB-2016 09:29:05      18-APR-2016 20:29:05                                  
17-FEB-2016 09:29:05      19-APR-2016 21:29:05                                  
17-FEB-2016 09:29:05      20-APR-2016 22:29:05                                  
17-FEB-2016 09:29:05      21-APR-2016 23:29:05                                  
17-FEB-2016 09:29:05      23-APR-2016 00:29:05                                  
17-FEB-2016 09:29:05      24-APR-2016 01:29:05                                  
17-FEB-2016 09:29:05      25-APR-2016 02:29:05                                  
17-FEB-2016 09:29:05      26-APR-2016 03:29:05                                  

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      27-APR-2016 04:29:05                                  
17-FEB-2016 09:29:05      28-APR-2016 05:29:05                                  
17-FEB-2016 09:29:05      29-APR-2016 06:29:05                                  
17-FEB-2016 09:29:05      30-APR-2016 07:29:05                                  
17-FEB-2016 09:29:05      01-MAY-2016 08:29:05                                  
17-FEB-2016 09:29:05      02-MAY-2016 09:29:05                                  
17-FEB-2016 09:29:05      03-MAY-2016 10:29:05                                  
17-FEB-2016 09:29:05      04-MAY-2016 11:29:05                                  
17-FEB-2016 09:29:05      05-MAY-2016 12:29:05                                  
17-FEB-2016 09:29:05      06-MAY-2016 13:29:05                                  
17-FEB-2016 09:29:05      07-MAY-2016 14:29:05                                  

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      08-MAY-2016 15:29:05                                  
17-FEB-2016 09:29:05      09-MAY-2016 16:29:05                                  
17-FEB-2016 09:29:05      10-MAY-2016 17:29:05                                  
17-FEB-2016 09:29:05      11-MAY-2016 18:29:05                                  
17-FEB-2016 09:29:05      12-MAY-2016 19:29:05                                  
17-FEB-2016 09:29:05      13-MAY-2016 20:29:05                                  
17-FEB-2016 09:29:05      14-MAY-2016 21:29:05                                  
17-FEB-2016 09:29:05      15-MAY-2016 22:29:05                                  
17-FEB-2016 09:29:05      16-MAY-2016 23:29:05                                  
17-FEB-2016 09:29:05      18-MAY-2016 00:29:05                                  
17-FEB-2016 09:29:05      19-MAY-2016 01:29:05                                  

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      20-MAY-2016 02:29:05                                  
17-FEB-2016 09:29:05      21-MAY-2016 03:29:05                                  
17-FEB-2016 09:29:05      22-MAY-2016 04:29:05                                  
17-FEB-2016 09:29:05      23-MAY-2016 05:29:05                                  
17-FEB-2016 09:29:05      24-MAY-2016 06:29:05                                  
17-FEB-2016 09:29:05      25-MAY-2016 07:29:05                                  
17-FEB-2016 09:29:05      26-MAY-2016 08:29:05                                  
17-FEB-2016 09:29:05      27-MAY-2016 09:29:05                                  
17-FEB-2016 09:29:05      28-MAY-2016 10:29:05                                  
17-FEB-2016 09:29:05      29-MAY-2016 11:29:05                                  
17-FEB-2016 09:29:05      30-MAY-2016 12:29:05                                  

START_TIME                END_TIME                                              
------------------------- -------------------------                             
17-FEB-2016 09:29:05      31-MAY-2016 13:29:05                                  

100 rows selected.

SQL> 

Proper date arithmetic can be performed and the dates sort correctly since Oracle now knows they are dates.

Of course the DATE datatype isn’t the only one that can be used; TIMESTAMP is also a valid choice for date data. Re-working the above example to use the TIMESTAMP datatype we see:


SQL> create table records(
  2  myid    number,
  3  mydata  varchar2(40),
  4  recorddt	     timestamp);

Table created.

SQL> 
SQL> begin
  2  	     for i in 1..1000 loop
  3  		     insert into records
  4  		     values(i, 'Record '||i, systimestamp - (4*i));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from records
  2  where
  3  recorddt > (select add_months(systimestamp, -24 )-1 from dual)
  4  order by recorddt;

      MYID MYDATA                         RECORDDT                              
---------- ------------------------------ ------------------------------        
       182 Record 182                     02-OCT-14 10.12.45.000000 AM          
       181 Record 181                     06-OCT-14 10.12.45.000000 AM          
       180 Record 180                     10-OCT-14 10.12.45.000000 AM          
       179 Record 179                     14-OCT-14 10.12.45.000000 AM          
       178 Record 178                     18-OCT-14 10.12.45.000000 AM          
       177 Record 177                     22-OCT-14 10.12.45.000000 AM          
       176 Record 176                     26-OCT-14 10.12.45.000000 AM          
       175 Record 175                     30-OCT-14 10.12.45.000000 AM          
       174 Record 174                     03-NOV-14 10.12.45.000000 AM          
       173 Record 173                     07-NOV-14 10.12.45.000000 AM          
       172 Record 172                     11-NOV-14 10.12.45.000000 AM          
...
      MYID MYDATA                         RECORDDT                              
---------- ------------------------------ ------------------------------        
        17 Record 17                      23-JUL-16 10.12.45.000000 AM          
        16 Record 16                      27-JUL-16 10.12.45.000000 AM          
        15 Record 15                      31-JUL-16 10.12.45.000000 AM          
        14 Record 14                      04-AUG-16 10.12.45.000000 AM          
        13 Record 13                      08-AUG-16 10.12.45.000000 AM          
        12 Record 12                      12-AUG-16 10.12.45.000000 AM          
        11 Record 11                      16-AUG-16 10.12.45.000000 AM          
        10 Record 10                      20-AUG-16 10.12.45.000000 AM          
         9 Record 9                       24-AUG-16 10.12.45.000000 AM          
         8 Record 8                       28-AUG-16 10.12.45.000000 AM          
         7 Record 7                       01-SEP-16 10.12.45.000000 AM          

      MYID MYDATA                         RECORDDT                              
---------- ------------------------------ ------------------------------        
         6 Record 6                       05-SEP-16 10.12.45.000000 AM          
         5 Record 5                       09-SEP-16 10.12.45.000000 AM          
         4 Record 4                       13-SEP-16 10.12.45.000000 AM          
         3 Record 3                       17-SEP-16 10.12.45.000000 AM          
         2 Record 2                       21-SEP-16 10.12.45.000000 AM          
         1 Record 1                       25-SEP-16 10.12.45.000000 AM          

182 rows selected.

SQL> 

Using the correct data type for the data being used is critical; storing data in a format other than what should be used can create problems with applications and can result in incorrect values. Analytical functions can produce erroneous output when data isn’t using the correct type, especially functions like lead(), lag() and sum() that can partition and order data to provide ‘rolling’ results. And if you can manipulate the data to provide correct answers it usually requires much more work than would be necessary using the proper data type. Keep that in mind when you design tables and applications.

Context is everything.

Advertisements

1 Comment »

  1. […] Post: That Ain’t Right […]

    Pingback by Top 50 Oracle SQL Blogs for 2016 - Complete IT Professional — May 2, 2016 @ 14:25 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: