"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.
Oracle Tips and Tricks -- David Fitzjarrell 