Re: Time studies
>From ilist@rmy.emory.edu Thu Jun 5 13:19 PDT 1997
>From: "Pat O'Connor" <poconnor@internetmci.com>
>Subject: Time studies
>Date: 5 Jun 1997 19:19:27 GMT
>To: informix-list@rmy.emory.edu
>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?
>TIA
>Pat O'Connor
>
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;
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>