From: jparker@hpbs3645.boi.hp.com (Jack Parker) [Leffler Filtration note - "It will be a useful record of the sorts of problem you have to deal with when using DATETIME and INTERVAL types."] My apologies in advance for the length of this. The situation: On our manufacturing shop floor circuit boards pass through a set of machines, inspection points, and other processes. After an inspection or test the board is marked as having passed or failed. The type of failure, and other key information as well as the DATETIME of the event are all stored. Further down the line is a group of folks who are charged with watching the process and looking for problems. If they see a number of similar failures in a given board it is an indication that the process may need fixing. The challenge is to present this data to them in a manner which highlights these clues. The 'spec': Present realtime failure data in detail and summary form on a work-shift basis. So the data is to be grouped by failure and station, and display a separate count for each shift (there is some other garbage as well, but in essence) : Summary: Board Part Board Location Station Failure Day Swing Graveyard with a separate count for each shift. - or - Detail: Board Part Board Location Station Failure Serial Number Furthermore, the operators need to be able to change the time window so that they can see what failed today for three shifts, or what failed yesterday for three shifts or even for the last n shifts. Needless to day this spec did not emerge full-blown. The challenge(s): - My familiarity with DATETIME is (was at this point) somewhat limited - The 'graveyard' shift starts at 10pm - there is no clean dividing line based on a date boundary. - Accentuate high failure rates. - Allow an 'effective'(as of this point) Date and time. A word about datetime/intervals: As most of you know a DATETIME identifies a moment in time. For example: On Oct 11, 1987 at 11:05:32.32AM my wife and I exchanged wedding vows. Ok the minute, seconds, and fractions are moot. But the 11:00 may be of interest to someone. So the point in time is really 10/11/87 at 11:00 oclock. Say we have a recurring event - an appointment which occurs every month at a particular time - say on the 4th at 11:30. In this case we don't care about the year or the month, just the day and the hour. A datetime datatype has the granularity to measure whatever you want. It can go from YEAR to FRACTIONs (5 decimal places) of a second - depending on your OS (syntax supports 5 places, but our OS only fills two of them). There is a reasonable discussion of all this in Appendix J of TFM (4gl ref). An Interval measures a time span. For example '2 hours ago'. While this is not a datetime concept, it uses much the same syntax and you can specify it in the same manner (YEARs through FRACTION). So 'how long have you been married' is an interval question. 'When did you get married' is a datetime question. Who in their right mind cares? Anyone who wishes to mark a specific moment in time. In our case our cross-reference data only sticks around for a couple of months, we also don't care about stuff down to the second. So we track it from MONTH TO MINUTE. (Obviously we'll have some problems in January, but I'm not too worried about it). There are some cute tricks you have to remember here. It makes no sense to 'add' two instants in time (or two datetimes) - what you want to do is take an instant in time (DATETIME) and add a time span (INTERVAL). "In two days I'm getting married". Sure this makes sense talking about it like this, but it's not 'intuitively obvious to the most casual observer' when working with the stuff. It's real easy to screw up what you put and where you put it. I probably wasted half of the time I spent on this trying to manipulate one datetime with respect to another datetime. The Beef: Let's try the detail statement first (easiest):SELECT some_data FROM some_tables WHERE some_conditions AND time_stmp > CURRENT - INTERVAL(24) UNITS HOURproblem 1 - obviously I'm not worried about doing this 'as of' a date yet. problem 2 - CURRENT cannot be specified as such, you have to tell it what portions of 'CURRENT' you care about. So:AND time_stmp > CURRENT MONTH TO HOUR - INTERVAL(24) UNITS HOURThis gives me everything for the last 24 hours. Ok, lets make this into a cursor we can open with variables so that we can change the 'as of' moment in time: LET sel_stmt = "SELECT some_data FROM some_tables ", "WHERE some_conditions ", "AND time_stmp BETWEEN ? - INTERVAL(?) UNITS HOUR ", "AND ? " Error1. You can't do that with an INTERVAL - it insists upon a hard coded value - not a place holder or a variable. Jonathan came to my rescue here and we worked out that you can: "AND time_stmp BETWEEN ? - ? ", "AND ? " IF you OPEN the cursor with a properly formatted INTERVAL. OR you can: "AND time_stmp BETWEEN ? - ? UNITS HOUR", "AND ? " IF you open the cursor with a properly formatted SMALLINT or INTEGER. Properly formatted means 99 or less. 3 places is a no no when talking about hours. Problem2. My first placeholder there assumes that I'm going to pass in a DATETIME of MONTH TO HOUR. From past experience there is no way I am going to ask a user to duplicate the formatting rules for a date time. You have no control over the field and they have to enter it according to the rules: "MM/DD HH". The error message is also not the cleanest in the world. It just tells you that something is wrong, not how to do it right. So - I am going to get the user to enter a DATE and a TIME and then put them together. "Enter effective date (MM/DD) [f000 ] and hour [f1]" This means that the day I'm going to pass to this statement is only a MONTH/DAY value. It also just so happens that I'm taking advantage of an EXTEND trick here. If you EXTEND a DATETIME, in this case EXTEND(DATETIME, MONTH TO HOUR) it will fill any trailing data fields (HOUR) with '0' and any leading fields (if I had included 'YEAR') with the current system value. So if I had EXTENDed(DATETIME, YEAR TO MINUTE) I would have gotten '94/08/12 00:00'. So: "AND time_stmp BETWEEN EXTEND(?, MONTH TO HOUR) + ? ", # I am going to pass an INTERVAL "AND EXTEND(?, MONTH TO HOUR) - ? " [ Other Leffler Filtration note: Perform as much as possible in terms of resolving the datetime arithmetic before opening the cursors in question. This will keep the engine from performing that arithmetic for each row. ] I am now going to open this by passing it: 1 - a DATETIME MONTH TO DAY 2 - An INTERVAL HOUR TO HOUR 3 - the same DATETIME MONTH TO DAY 4 - Another INTERVAL HOUR TO HOUR In other words BETWEEN midnight some morning + so many hours/minutes and midnight some morning - however far back I'm looking. I'm now asking for three time points: "Enter effective date (MM/DD) [f000 ] and hour [f01] for [f02] preceding hours" Oops - there's a bug, my second time there is taking the 'preceding hours' from midnight some morning instead of from the 'as of' time. So: "AND time_stmp BETWEEN EXTEND(?, MONTH TO HOUR) + ? ", "AND EXTEND(?, MONTH TO HOUR) + ? - ?" (I could calculate the ? - ? before going in, but what if they ask for an as of 16 hours for the past 4 hours? Ooooh, can we do negative intervals? if we can then this could be expressed as: "AND EXTEND(?, MONTH TO HOUR) + ?" Turns out that we can indeed use negative INTERVALs - as long as your DATETIME won't become negative through adding the negative INTERVAL. Negative DATETIMEs are illegal.) I am now going to open this by passing it: 1 - a DATETIME MONTH TO DAY # as of date 2 - An INTERVAL HOUR TO HOUR # as of time 3 - the same DATETIME MONTH TO DAY 4 - the same INTERVAL HOUR TO HOUR - Another INTERVAL HOUR TO HOUR # 'preceding hours' NEXT problem! The generic user is always going to want to see 'as of now' for the preceding 24 hours - we need a default: Easy right? Just use CURRENT - ah, but CURRENT is a DATETIME, remember I need this expressed as a DATETIME MONTH TO DAY + an INTERVAL HOUR TO HOUR. How Do we get the CURRENT hour and minute into an INTERVAL? LET INTERVAL = CURRENT HOUR TO HOUR ? dream on. This works: DEFINE i DATETIME HOUR TO HOUR, j INTERVAL HOUR TO HOUR, k CHAR(2) LET i = CURRENT HOUR TO HOUR LET k = i LET j = k UNITS HOUR Of course this doesn't work if you use an HOUR TO MINUTE, but you can mix things up until it does. (Those of you who want extra credit will note that I also had to add 1 hour to the CURRENT value so that anything in the past hour would be included) NEXT PROBLEM!!!!! The statement now runs, but guess what - it doesn't return any data. This is because BETWEEN doesn't work in this situation. If we change the statement to:AND time_stmp > EXTEND(?, MONTH TO HOUR) + ? AND time_stmp < EXTEND(?, MONTH TO HOUR) + ?and pass in: 1 - a DATETIME MONTH TO DAY # as of date 2 - An INTERVAL HOUR TO HOUR # as of time 3 - the same DATETIME MONTH TO DAY 4 - the same INTERVAL HOUR TO HOUR - Another INTERVAL HOUR TO HOUR All is well. [Other other Leffler filtration note: (loosely interpreted by JP) Jack is a dunce. Between only works when the first value is less than the second value. If parameters 4 and 2 were reversed all would have been fine. ] It's a good thing I worked this all out on the simple statement. Now comes the fun one - the summary statement. This one has to COUNT(*) and GROUP. Not only that, but since it has to report three totals (one for each shift) per line, it needs to UNION as well:"SELECT some_date, count(*) sum_count, 0, 0 FROM some_tables ", "WHERE some_conditions ", "AND time_stmp < EXTEND(?, MONTH TO MINUTE) + ? ", "AND time_stmp > EXTEND(?, MONTH TO MINUTE) + ? ", "AND EXTEND(time_stmp, HOUR TO MINUTE) between ", "DATETIME(6:00) HOUR TO MINUTE AND DATETIME(14:00) HOUR TO MINUTE ", "GROUP BY some_data ", "UNION ", "SELECT some_date, 0, count(*) sum_count, 0 FROM some_tables ", ............I include this because of two tricks. 1 - the 'count(*) sum_count' - later when reading this array I will check to see if the board has changed from the last read row, if not then I'll move this counter (sum_count) into that data row and re-read. This way I'll get the three totals on one line. (I'm also going to sort it based on the total number of failures so that high failure rates show up first, but yah-di-yah-di-ya.) 2 - Remember that our time_stmp is a MONTH TO MINUTE field. How can I do a comparison here when the shift crosses a day boundary?"AND (EXTEND(time_stmp, HOUR TO MINUTE) > DATETIME(22:00) HOUR TO MINUTE ", "OR EXTEND(time_stmp, HOUR TO MINUTE) < DATETIME(6:00) HOUR TO MINUTE ) "The trick is that EXTEND not only EXTENDS datetimes, it can also shrink them. In this case it just tossed the MONTH/DAY data out the window. So the day boundary is no longer important. Based on the Leffler Filtration notes: Cursor 1 (detail cursor) - put the DATETIME and INTERVAL together before opening the cursor - Fix so that the first value is lower than the second. - Original code:AND time_stmp > EXTEND(?, MONTH TO HOUR) + ? AND time_stmp < EXTEND(?, MONTH TO HOUR) + ?- Corrected code:AND time_stmp BETWEEN ? AND ? - Better fix the open variables: DEFINE s_date, e_date DATETIME MONTH TO HOUR as_of_day DATETIME MONTH TO DAY, as_of_hour, junk INTERVAL HOUR TO HOUR shifts SMALLINT # cardinal rule of DATETIME/INTERVAL - don't do too much at a time: # LET INTERVAL = x UNITS HOUR + y UNITS MINUTE will yield # (x+y UNITS HOUR):00:00 LET e_date = as_of_day + as_of_hour LET junk = shifts * 8 UNITS HOUR # shifts must be < 12.5 LET s_date = e_date - junk So: OPEN cursor USING s_date, e_dateI could do this with the other select statement as well, but I'm sure you get the picture. j. _____________________________________________________________________________ Jack Parker | Hewlett Packard, BSMC Boise, Idaho, USA| He who talks most, talks least. jparker@hpbs3645.boi.hp.com |