tags:

views:

277

answers:

3
A: 

I see no easy way to create non-existing records out of thin air, but you could create yourself a point_dates table containing all the timestamps you're interested in, and left join it on your data:

select pd.slot, IFNULL(data_point, 0) from point_dates pd left join some_table st on st.point_date=pd.slot where point_date >= start_date AND point_date < end_date order by point_date

ggiroux
A: 

You cannot query data you do not have.

You (as a thinking person) can claim that the 00:20 data is missing; but there's no easy way to define "missing" in some more formal SQL sense.

The best you can do is create a table with all of the expected times.

Then you can do an outer join between expected times (including a 0 for 00:20) and actual times (missing the 00:20 sample) and you'll get kind of result you're expecting.

S.Lott
+1  A: 

Yes, you can do that using SQL only. A solution would be to use a Stored Routine. The bellow Stored Procedure produces following output:

start   cnt
00:05:00   1
00:10:00   0
00:15:00   1
00:20:00   0
00:25:00   1
00:30:00   0
00:35:00   1
00:40:00   0
00:45:00   0
00:50:00   0
00:55:00   2

The table I used:

CREATE TABLE `timedata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` datetime DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Here the Stored Procedure (adjust for your environment):

DROP PROCEDURE IF EXISTS per5min;
DELIMITER //
CREATE PROCEDURE per5min ()
BEGIN
  DECLARE dtMin DATETIME;
  DECLARE dtMax DATETIME;
  DECLARE dtStart DATETIME;
  DECLARE dtStop DATETIME;
  DECLARE tmDiff TIME;
  DECLARE result INT UNSIGNED;
  SET @offset = 5 * 60;
  SELECT MIN(c1) into dtMin FROM timedata;
  SELECT MAX(c1) into dtMax FROM timedata;

  CREATE TEMPORARY TABLE tmp_per5min (
      start TIME,
      cnt INT UNSIGNED
  );

  SET dtStart = dtMin;
  REPEAT
    SELECT dtStart + INTERVAL @offset SECOND into dtStop;
    SELECT count(c2) into result FROM timedata WHERE c1 BETWEEN dtStart and dtStop;
    SELECT TIME(SUBTIME(dtStop,TIME(dtMin))) into tmDiff;
    INSERT INTO tmp_per5min (start,cnt) VALUES (tmDiff,result);
    SET dtStart = dtStop;
  UNTIL dtStop >= dtMax END REPEAT;

  SELECT * FROM tmp_per5min;
  DROP TABLE tmp_per5min;
END;
//
DELIMITER ;

CALL per5min();

If you save the above into a file called 'per5minproc.sql', you can load it like this:

shell> mysql -uroot test < per5minproc.sql

In Python using MySQLdb (I didn't get this working in MySQL Connector/Python, me ashamed!):

import MySQLdb as m

if __name__ == '__main__':
    db = m.connect(user='root',db='test')
    c = db.cursor()
    c.callproc("per5min")
    print(c.fetchall())
    c.close()
    db.close()

The solution above works, but probably will need some tweaking, e.g. dtStart can be an argument to the SP. And, it's indeed all SQL!

geertjanvdk
The above stored procedure was made quickly, so it there might a few bugs.. but it might get you going!
geertjanvdk