[EMAIL PROTECTED] ("Paul Halliday") wrote in
news:[EMAIL PROTECTED]:
> Could you expand a little on how that works?
USE test;
DROP TABLE IF EXISTS event;
CREATE TABLE event (timestamp DATETIME NOT NULL);
INSERT INTO event (timestamp) VALUES
('2007-01-09 20:02:15'),
('2007-01-09 20:02:15'),
('2007-01-09 20:03:20'),
('2007-01-09 20:08:33'),
('2007-01-09 20:08:33'),
('2007-01-09 20:12:19'),
('2007-01-09 20:18:59'),
('2007-01-09 20:23:03'),
('2007-01-09 20:50:11'),
('2007-01-09 20:50:11'),
('2007-01-09 20:50:12'),
('2007-01-09 20:50:12'),
('2007-01-09 20:50:12'),
('2007-01-09 20:50:12'),
('2007-01-10 01:16:09'),
('2007-01-10 01:18:31'),
('2007-01-10 03:00:22'),
('2007-01-10 03:09:23'),
('2007-01-10 03:12:23'),
('2007-01-10 03:15:23'),
('2007-01-10 03:18:23'),
('2007-01-10 03:30:24');
DROP TABLE IF EXISTS ints;
CREATE TABLE ints (i INT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO ints (i) VALUES
( 0), ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
(20), (21), (22), (23), (24), (25), (26), (27), (28), (29);
SET @interval := 1*60*60; -- 1 hour in seconds
SET @min_dt := '2007-01-09 20:00:00';
SET @max_dt := '2007-01-10 19:59:59';
SELECT
@min_dt + INTERVAL ( i)[EMAIL PROTECTED] SECOND AS S,
@min_dt + INTERVAL (1+i)[EMAIL PROTECTED] - 1 SECOND AS E,
DATE_FORMAT(@min_dt + INTERVAL (i)[EMAIL PROTECTED] SECOND, '%H') AS H,
COUNT(timestamp)
FROM ints
LEFT JOIN event ON event.timestamp
BETWEEN @min_dt + INTERVAL ( i)[EMAIL PROTECTED] SECOND AND
@min_dt + INTERVAL (1+i)[EMAIL PROTECTED] - 1 SECOND
WHERE
@min_dt + INTERVAL ( i)[EMAIL PROTECTED] SECOND < @max_dt
GROUP BY S, E
ORDER BY S;
--
felix
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
