Re: Time studies

Oh, and since it uses 2 stored procedures, it isn't any help if you have a 4.1x engine -- but you are using a 5.0x engine, aren't you! Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }Date: Thu, 5 Jun 1997 14:10:23 -0700 }From: johnl@informix.com (Jonathan Leffler) } }>From: "Pat O'Connor" <poconnor@internetmci.com> }>Date: 5 Jun 1997 19:19:27 GMT }>X-Informix-List-Id: <news.38815> }> }>I have a number of rows in a table that contain a column defined as: }> datetime hour to minute }>I want to be able to count the number of rows (total the dollars) in a }>5-minute increment (or any other minutes increment). }>I have Informix SE 4.1 with ISQL & R4GL. }>Can anyone help me with query syntax? } }Tain't trivial, but the following seems to do the trick: } }CREATE TABLE timers }( } t DATETIME HOUR TO MINUTE NOT NULL, } v INTEGER NOT NULL }); } }INSERT INTO timers VALUES('12:00', 150); }INSERT INTO timers VALUES('12:01', 140); }INSERT INTO timers VALUES('12:02', 133); }INSERT INTO timers VALUES('12:03', 110); }INSERT INTO timers VALUES('12:04', 130); }INSERT INTO timers VALUES('12:05', 130); }INSERT INTO timers VALUES('12:05', 150); }INSERT INTO timers VALUES('12:06', 180); }INSERT INTO timers VALUES('12:07', 188); } }CREATE PROCEDURE dt_minutes(d DATETIME HOUR TO MINUTE) RETURNING INTEGER; } DEFINE i INTERVAL MINUTE(4) TO MINUTE; } DEFINE s CHAR(5); } DEFINE n INTEGER; } } LET i = d - DATETIME(0:0) HOUR TO MINUTE; } LET s = i; } LET n = s; } RETURN n; }END PROCEDURE; } }SELECT TRUNC((dt_minutes(t)+4)/5) AS grouping, } SUM(v) AS value } FROM timers } GROUP BY 1 } ORDER BY 1; } }It produces the output: } }144|150 }145|793 }146|368 } }Of course, if you want to convert the grouping number back into a }datetime, then there's a bit more work to do: } }CREATE PROCEDURE to_dt_hm(i INTEGER) RETURNING DATETIME HOUR TO MINUTE; } DEFINE d DATETIME HOUR TO MINUTE; } LET d = DATETIME(0:0) HOUR TO MINUTE + i UNITS MINUTE; } RETURN d; }END PROCEDURE; } }SELECT } to_dt_hm(5*TRUNC((dt_minutes(t)+4)/5)) AS START, } SUM(v) AS value } FROM timers } GROUP BY 1 } ORDER BY 1;