DATETIME, INTERVAL and other tricks

I wrote this and passed it through the Leffler Filtration process. Jonathan pointed out a couple of faults in my logic which I have included as appropriate and built into my final statements at the end. j. ----- I wish I had written down my thoughts as I went through this process, it would have been easier to recreate. The following is a summary of stuff I went through for a couple of stolen hours per day for about the last week. I also pestered Jonathan a great deal and finally solved this with his help. The following discussion is about something I'm sure 10% of you know about, another 15% will read and say "that's pretty obvious Jack", and perhaps 5% of you will actually care. But I'm going to do it anyway, because in the future I want some benchmark to come back to, it will solidify it in my own mind and there are 5% of you who might care. [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 HOUR problem 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 HOUR This 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 s