Toad World® Forums

SQL Logic for Time Difference

Greetings,

I need to find out all exams that concluded over 72 hours ago but for which final grades have not been posted. The relevant data in 2 columns are Exam Date (DATE) & Exam_End_Time (VARCHAR2 (11 Char)).

I am trying to figure out two things - first, logic that will allow me to capture all exams that concluded over 72 hours ago from current time and second how to display this time in hours. Currently I am hard coding my time difference logic (trunc (B.SYEXAMS_EXAM_DATE) < trunc (To_date (‘03/06/2016’, ‘DD/MM/YYYY’))). I would like to make this logic dynamic where my script would calculate time based on sysdate.

My display logic does not give me 100% accurate answer either. My display logic is as follows:

round(uos_f_time_diff_in_seconds (to_date(to_char(decode(B.SYEXAMS_EXAM_DATE,null,sysdate,B.SYEXAMS_EXAM_DATE),‘DD-MM-YYYY’)||’ '||substr(B.SYEXAMS_TIME_TO, 1,

length(SYEXAMS_TIME_TO)-2),‘DD-MM-YYYY HH:MI:SS’) ,sysdate)/3600,0) HOURS_PASSED,

Any assistance provided would be appreciated.

My current script is as follows:

select A.SFRSTCR_TERM_CODE TERM,

C.SSBSECT_SUBJ_CODE||C.SSBSECT_CRSE_NUMB CRSE_NUM,

A.SFRSTCR_CRN CRN,

C.SSBSECT_SEQ_NUMB SEC,

D.SCBCRSE_TITLE COURSE_TITLE,

C.SSBSECT_SCHD_CODE TYPE,

A.SFRSTCR_CAMP_CODE CAMP,

E.STVCOLL_DESC COLLEGE,

C.SSBSECT_ENRL ENRL,

count (distinct A.SFRSTCR_PIDM) NO_GRADE,

B.SYEXAMS_EXAM_DATE EXAM_DATE,

B.SYEXAMS_TIME_FROM START_TIME,

B.SYEXAMS_TIME_TO END_TIME,

–trunc(sysdate) - trunc(To_Date (B.SYEXAMS_EXAM_DATE)) DAYS_PASSED,

round(uos_f_time_diff_in_seconds (to_date(to_char(decode(B.SYEXAMS_EXAM_DATE,null,sysdate,B.SYEXAMS_EXAM_DATE),‘DD-MM-YYYY’)||’ '||substr(B.SYEXAMS_TIME_TO, 1,

length(SYEXAMS_TIME_TO)-2),‘DD-MM-YYYY HH:MI:SS’) ,sysdate)/3600,0) HOURS_PASSED,

UCS_PKG.GET_PIDM_ID(F.SIRASGN_PIDM) INSTR_ID,

G.SPRIDEN_FIRST_NAME||’ '||G.SPRIDEN_LAST_NAME INSTR_NAME,

H.GOREMAL_EMAIL_ADDRESS INSTR_EMAIL_ADDRESS

from SFRSTCR A, SYEXAMS B, SSBSECT C, SCBCRSE D, STVCOLL E, SIRASGN F, SPRIDEN G, GOREMAL H

where A.SFRSTCR_TERM_CODE = B.SYEXAMS_TERM_CODE

and A.SFRSTCR_TERM_CODE = C.SSBSECT_TERM_CODE

and A.SFRSTCR_CRN = B.SYEXAMS_CRN

and A.SFRSTCR_CRN = C.SSBSECT_CRN

and trunc (B.SYEXAMS_EXAM_DATE) < trunc (To_date (‘03/06/2016’, ‘DD/MM/YYYY’))

and A.SFRSTCR_RSTS_CODE in (‘RE’, ‘RW’, ‘RF’)

and A.SFRSTCR_GRDE_CODE is null

and A.SFRSTCR_TERM_CODE = ‘201520’

and C.SSBSECT_SUBJ_CODE = D.SCBCRSE_SUBJ_CODE

and C.SSBSECT_CRSE_NUMB = D.SCBCRSE_CRSE_NUMB

and D.SCBCRSE_EFF_TERM = (select max(E.SCBCRSE_EFF_TERM)

from SCBCRSE E

where D.SCBCRSE_SUBJ_CODE = E.SCBCRSE_SUBJ_CODE

and D.SCBCRSE_CRSE_NUMB = E.SCBCRSE_CRSE_NUMB

)

and D.SCBCRSE_COLL_CODE = E.STVCOLL_CODE(+)

and C.SSBSECT_TERM_CODE = F.SIRASGN_TERM_CODE

and C.SSBSECT_CRN = F.SIRASGN_CRN

and F.SIRASGN_PIDM = H.GOREMAL_PIDM(+)

and H.GOREMAL_EMAL_CODE = ‘20’

and H.GOREMAL_STATUS_IND = ‘A’

and F.SIRASGN_PIDM = G.SPRIDEN_PIDM

and G.SPRIDEN_CHANGE_IND is null

Group by A.SFRSTCR_TERM_CODE, C.SSBSECT_SUBJ_CODE||C.SSBSECT_CRSE_NUMB, A.SFRSTCR_CRN, C.SSBSECT_SEQ_NUMB, D.SCBCRSE_TITLE, C.SSBSECT_SCHD_CODE, E.STVCOLL_DESC, A.SFRSTCR_CAMP_CODE, C.SSBSECT_ENRL, B.SYEXAMS_EXAM_DATE, B.SYEXAMS_TIME_FROM, B.SYEXAMS_TIME_TO, UCS_PKG.GET_PIDM_ID(F.SIRASGN_PIDM),

G.SPRIDEN_FIRST_NAME||’ '||G.SPRIDEN_LAST_NAME, H.GOREMAL_EMAIL_ADDRESS,

round(uos_f_time_diff_in_seconds (to_date(to_char(decode(B.SYEXAMS_EXAM_DATE,NULL,sysdate,B.SYEXAMS_EXAM_DATE),‘DD-MM-YYYY’)||’ '||substr(B.SYEXAMS_TIME_TO, 1,

length(B.SYEXAMS_TIME_TO)-2),‘DD-MM-YYYY HH:MI:SS’) ,sysdate)/3600,0)

order by round(uos_f_time_diff_in_seconds (to_date(to_char(decode(B.SYEXAMS_EXAM_DATE,null,sysdate,B.SYEXAMS_EXAM_DATE),‘DD-MM-YYYY’)||’ '||substr(B.SYEXAMS_TIME_TO, 1,

length(B.SYEXAMS_TIME_TO)-2),‘DD-MM-YYYY HH:MI:SS’) ,sysdate)/3600,0) desc, H.GOREMAL_EMAIL_ADDRESS

;

Function

CREATE OR REPLACE FUNCTION UOSINST1.“UOS_F_TIME_DIFF_IN_SECONDS” (

DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1 NUMBER;

NDATE_2 NUMBER;

NSECOND_1 NUMBER(10,5);

NSECOND_2 NUMBER(10,5);

– DATE_1 => START DATE

– DATE_2 => END DATE

BEGIN

IF DATE_2>DATE_1

THEN

– Get Julian date number from first date (DATE_1)

NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, ‘J’));

– Get Julian date number from second date (DATE_2)

NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, ‘J’));

– Get seconds since midnight from first date (DATE_1)

NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, ‘SSSSS’));

– Get seconds since midnight from second date (DATE_2)

NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, ‘SSSSS’));

RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));

ELSE RETURN 0;

END IF;

END uos_f_time_diff_in_seconds;

/

So, SYEXAMS_TIME_TO is a date representing when the exam ended? If so…

To display the time passed in hours, I would select (SYSDATE - SYEXAMS_TIME_TO) * 24 AS HOURS_PASSED. This works because Oracle stores dates as numbers. The “Ones” digit represents days.

and to limit to exams that concluded over 72 hours ago…

WHERE SYEXAMS_TIME_TO < (SYSDATE - 3)

Hi John,

Thanks for responding promptly.

B.SYEXAMS_TIME_TO datatype is (VARCHAR2 (11 Char)). For relevancy, B.SYEXAMS_EXAM_DATE datatype is (DATE).

When I use your suggested logic ‘WHERE SYEXAMS_TIME_TO < (SYSDATE - 3)’, I get the “ORA-01843: not a valid month” error.

I need to combine column B.SYEXAMS_EXAM_DATE & column B.SYEXAMS_TIME_TO to get date & time. Once I have the correct date & time, then I need to subtract the correct date & time from systdate (current time) and display the result in hours.

Hi Jolly Doe,

You need to convert the character dates into proper dates before working with them. Do you know the format that they are stored in? If so, we can use TO_DATE (SYEXAMS_TIMES_TO, ‘YYYYMMDD’) assuming that the stored dates are indeed YYYYMMDD format.

In our comparison, you are getting the error because Oracle is using the current setting of NLS_DATE_FORMAT to use as the date format specifier in the implicit call to TO_DATE () which is being called on your column. The current setting obviously does not match the or mat your dates are stored in in the character column.

If you have any choice in the matter, don’t ever do this sort of thing. It is a really broken thing to do and messes with the optimiser’s mind!

Some excellent advice for anyone thinking of storing date values in anything that is not a date or timestamp data type. :wink:

https://richardfoote.wordpress.com/2016/05/26/storing-date-values-as-characters-whats-really-happening/

https://richardfoote.wordpress.com/2016/05/30/storing-date-values-as-characters-part-ii-a-better-future/

https://richardfoote.wordpress.com/2016/06/01/storing-date-values-as-numbers-the-numbers/

Cheers,

Norm. [ TeamT ]

Sent from my Android device with K-9 Mail. Please excuse my brevity.

P.S. if you need to find a date 72 hours ago, sysdate-3 will work, but it will be to the exact second. So if sysdate is 06 June 2016 at 16.25 and 12 seconds then 03 June 2016 at that exact same time is your 3 days ago.

If you just want any time on the day that was three days ago, then you should use trunc(sysdate)-3 instead.

HTH

Cheers,

Norm. [ TeamT ]

Sent from my Android device with K-9 Mail. Please excuse my brevity.

Hi Norm,

When I try to convert SYEXAMS_TIMES_TO date format using TO_DATE (SYEXAMS_TIMES_TO, ‘YYYYMMDD’), I get the ‘ORA-00904: “SYEXAMS_TIMES_TO”: invalid identifier’ error.

The currently displayed distinct values in SYEXAMS_TIMES_TO field are as follows:

SYEXAMS_TIME_TO

–:--

1:00

01:00PM

1:30

1:30 PM

1:30:00

01:30PM

2:00

03:00PM

3:30

3:30 PM

03:30PM

04:00PM

4:30

5:00

5:00 PM

05:00:00 ?

05:00PM

5:30

5:30:00

6:00

6:00 PM

6:00:00

06:00:00 ?

06:00PM

06:30:00 ?

06:30PM

07:00:00 ?

07:00PM

10:00

10:00 AM

10:30

10:30:00 AM

10:30AM

11:00

11:00 AM

11:00:00

11:00:00 ?

11:00AM

12:00

12:00PM

12:45 PM

13:00

13:00 PM

13:00:00

13:00PM

14:00

14:00 PM

14:00:00

15:00:00 ?

15:30

16:00

16:00 PM

16:30

16:30 PM

1700

17:00

17:00 PM

17:30

18:00

18:00 PM

18:40 PM

18:59 PM

19:00 PM

19:30 PM

1:00

1:00:00 PM

20:00 PM

21:00 PM

2:00

3:30

3:30:00 PM

4:30

4:30:00 PM

5:00

5:00PM

5:30

5:45

6:00

6:00:00 PM

6:30

7:00:00 PM

Hopefully the above table gives you an idea of the format.

I think your suggestion ”In our comparison, …” is what is happening and it is what I need to fix.

As you guessed, I have no say in the formation of the table that stores SYEXAMS_TIMES_TO data. What I have to do it figure out how to resolve this issue without altering current database setup.

I tried to follow up on the advice by Richard Foote but it was a bit too technical for my ability/access. I work as an IT coordinator/analyst. To me, Richard’s advice is meant for DBAs.

Morning Jollydoe,

You can only use the YYYYMMDD format specifier if your dates are stored in that format. Looking at your table of distinct values, you have got no standard format at all, so you are pretty much up that famous creek, and you don’t even have a canoe, never mind a paddle! :wink:

In fact, the data shows that your application does little or no validation or sanity checking on that part of the data. I wonder what else is broken?

Your column looks to only hold time portions, and not date time values. This makes it impossible, unless there’s another column with the date part, to calculate 3 days or 72 hours ago.

Your query to find 72 hors ago simply cannot work with the data shown. Sorry.

You need to cleanse your data, and get it into a standard format before you do anything. Where to start? Is 2:00 equal to 2 am or 2 pm? There are question marks in some time values, and they show seconds as well as hours and minutes.

I would suggest adding a proper date column to the table and converting your times into it in a proper format. Hh24:mi would suffice. But oracle will do that for you anyway. Once you find out what the character values should represent in date format that is.

Richards blog is indeed quite technical, databases are. But the point is, you should never ever store a value of a particular type, as something else. Dates are dates (well, that are date and time actually) and not strings, not number, not anything else!

The problems you now have show just how accurate Richards articles are! Even without digging deep into what the optimiser does.

Cheers,

Norm. [ TeamT ]

Sent from my Android device with K-9 Mail. Please excuse my brevity.

Greetings Norm,

I realized how screwed up the data was before I posted it but I wanted to see if there was any solution to it. Based on your comment, it seems like there could be a solution. There is another column with the date part. As you will see from the script, the column is

B.SYEXAMS_EXAM_DATE (datatype = (date))

Another positive aspect is uniformity of data for the time duration I need. I am only looking for exams that took place in the last month and this month. As such, when I restrict the time duration to beginning of 2016 , the distinct values for B. SYEXAMS_TIME_TO are as follows:

10:30AM

11:00AM

12:00PM

01:00PM

01:30PM

03:00PM

03:30PM

04:00PM

05:00PM

06:00PM

06:30PM

07:00PM

Ideally I would like to add these times to the corresponding B.SYEXAMS_EXAM_DATE making it one column with date & time and then subtract sysdate & time from it to get the difference.

I will talk to the developer of the program to see if data could be cleaned.

Afternoon Jollydoe,

ok, I’ve looked back on this thread and it appears that you do have a date column for the exam day - SYEXAMS_EXAM_DATE. Hooray! So we can now work out the actual date and time that the exam took place.

Also given that you sya that you cannot adjust the database structure at all, I think you still do have a say in the matter, in fact, I would be camping on the developers/designer’s doorstep until I could put it to him/her/them that they have failed miserably in their job of getting only valid data into the database (developers) and that the design is flawed in that it needs two columns to store dates and times when one will do. And that’s not to mention using the wrong data type to store them!

Is the column SYEXAMS_EXAM_DATE a DATE data type? Please say yes! If not, all bets are off! If that too is a string, it would be interesting to see what atrocious data is in there also - if the time portion is anything to go by!

We have the following values in the data you have shown. AM indicates either AM or PM with or without a leading space. HH indicates a one or two digit hour in 12 hour format, HH24 indicates a one or two digit hour in 24 hour/zulu/military format. MI indicates minutes, one or two digits and SS indicates seconds - which are always zero and are conveniently ignorable!

The separator, thankfully, is always a colon (:slight_smile: except for when it is missing! Which it is in some rows.

–:--
HH24MM
HH:MI
HH:MI AM
HH24:MI: AM
HH:MI:SS AM
HH:MI:SS
HH:MI:SS ?

etc

  • First problem, get rid of the spurious entries (–:--) and any NULLS.
  • Then we must deal with those annoying question marks in some of the times.
  • Then there are times like ‘21:00 PM’ which should be ‘09:00 PM’ as an AM/PM only allows hours between 0 and 12.
  • If the hours part is ever greater than 23, we must barf.
  • Then we have times with and without the AM/PM indicator. We must assume AM unless the hours is greater than 12, in which case, it’s PM.
  • And finally, there are times with no colon to separate hours from minutes. We will have a three or four digit string. When four digits are found, we have HHMI and when three we either have HMI or HH and one MI digit. As we cannot differentiate between the two, we must assume HMI rather than HH and one MI digit.
  • If the minutes part is ever greater than 59, we must barf.
    At the end, we should end up with a time which is in the exact format of ‘HH:MI AM’ - again, the AM part signifies Am or PM.

I would say that you definitely need a function to separate out this lot, trying to do it in a single SQL statement I leave as an “exercise for the reader” as they say.

Anyway, the attached file is a function that you can call to convert the time column into a valid time column, or NULL, or a desired error string:

select interpret_time_values(‘1:00:00 PM’) as valid_time from dual;
select interpret_time_values(‘1:47’) as valid_time from dual;
select interpret_time_values(‘123’) as valid_time from dual;
select interpret_time_values(‘1723’, ‘FUTTOX’) as valid_time from dual;
select interpret_time_values(‘66x’, ‘OOPS!’) as valid_time from dual;
select interpret_time_values(‘24:00’, ‘OOPS!’) as valid_time from dual;
select interpret_time_values(‘2415’, ‘OOPS!’) as valid_time from dual;

These all work as expected. I created a small test table with a column named ‘A_TIME’ which contained all the values yo posted in a previous message. All of them are converted correctly.

You will need to change the code in a couple of places. My parameters etc are “anchored” so that instead of saying VARCHAR2(11) to match the table definition, I say “table_name.coulumn_name%type” which means that if you ever change the table definition for that particular column, the function will go invalid, but will automatically recompile on the next call to it and it will then pick up and use the new column definition.

Look for the text “norman.a_time%type” and change it to suit your table and column names.

Given that your date column is indeed a date, we can now do this:

select to_char(trunc(SYEXAMS_EXAM_DATE), ‘yyyy/mm/dd’) || ’ ’ || interpret_time_values(SYEXAMS_TIMES_TO) as valid_time from your_table_name;

Which will give you a VARCHAR2 in the correct format for a ‘yyyy/mm/dd hh:mi AM’ format specifier in TO_DATE().

Which means this is now possible:

select stuff from table
where sysdate - to_date( to_char(trunc(SYEXAMS_EXAM_DATE), ‘yyyy/mm/dd’) || ’ ’ || interpret_time_values(SYEXAMS_TIMES_TO), ‘yyyy/mm/dd hh:mi AM’) >= 3;

Or whatever it is you need to do.

HTH

Cheers,

Norm. [TeamT]

JollyDoe_Time_conversion.sql (6.82 KB)

Sorry, I pressed POST too soon.

  • The function returns valid times in the format ‘HH:MI AM’ as detailed above.
  • Error values (hours > 23, minutes > 59, invalid characters in the hours or minutes part) return your chosen error string. (the second parameter to the function). This defaults to '00:00 AM’which is an invalid time.
  • NULL will be returned if, and only if, the passed time string is NULL, or is that ‘–:--’ value.

Cheers.

Norm. [TeamT]

Hi Jollydoe,

I will talk to the developer of the program to see if data could be cleaned.

Basically, the data has to be cleaned and there needs to be check or other constraints, triggers etc, added to ensure that whatever is being stored is in a valid time format. The ideal solution is to combine the two columns into one date type column. Oracle will validate the dates for you and pplicaton code will be less weird, having to call data cleansing functions on each select etc.

When I saw the amount of weird data all supposedly representing a time, it almost made me weep! The function I uploaded at lunchtime will clean up the current pile of poo (technical term) but who is to say what other formats the users will think up in the future?

Speaking of constraints, it’s perfectly fine for the application to enforce business rules, but the database has to have the final say in the matter! You must keep the constraints, triggers etc that are enforcing the rules as close as possible to the data. They live in the database. Even if they so appear in the application.

The applicaton must be coded in such a way as to handle any database exceptions properly, allowing the user the ability to correct and resubmit their data without having to re-enter it all from the start each time!

HTH

Cheers,

Norm. [ TeamT ]

Sent from my Android device with K-9 Mail. Please excuse my brevity.

Hi Norm,

Thank you for the detailed explanation and SQL script. Please give me some time to go over it, work with the developer to fix the incorrect data type issue, and resolve my time difference logic issue. I will get back to you once I am done with these three items.

Hi Norm,

Let me respond to your last 3 messages in a bit more detail.

I have not only brought it to the concerned manager’s attention, I have also made our boss (we both report to the same boss) aware of it. My rationale behind fixing this issue now is so that we do not have to go through the same issues next term when I have to submit these reports again.

Thank you again for creating unique time formats based on the data I shared, writing the function, and explaining it to me how it is going to work. I will make appropriate changes to both the data set and the function and run them in our test environment to see the results.

Even though I do not have experience writing these functions/programs, I have worked with data sets that are very similar to this data in my previous jobs but without all the issues. As such, I am assuming that the constraints, triggers, business rules, and data integrity you mentioned were maintained by the team that managed the program in my previous jobs but not being maintained in my current job.

I have come across other places in our student database with data issues. Maybe this is a good start. If I can successfully fix this issue, maybe our boss would want me to fix other data issues.

As stated in my previous message, once I have fixed all issues and resolved my time difference logic, I will update you.

Ciao!

Evening Jollydoe,

Thanks for letting me know. Good luck with sorting it all out.

Cheers,

Norm. [ TeamT ]

Sent from my Android device with K-9 Mail. Please excuse my brevity.