MySQL GROUP BY and Fill Empty Rows

by

In this answer I will outline how to generate your calendar tables.

Create three tables for days, hours and minutes:

CREATE TABLE days (
  day DATE,
  PRIMARY KEY (day)
) 
CREATE TABLE hours (
  hour INT,
  PRIMARY KEY (hour)
) 
CREATE TABLE minutes (
  minute INT,
  PRIMARY KEY (minute)
)

Fill the hours table with the numbers from 0 to 23 and the minutes table with the numbers from 0 to 59. To fill the days table, you can create a procedure like the following:

CREATE PROCEDURE make_days(IN start_date DATE, IN end_date DATE)
BEGIN
  DECLARE curr_date DATE;
  SET curr_date = start_date;
  WHILE curr_date <= end_date DO
    INSERT IGNORE INTO days(day)  VALUES(curr_date);
    SET curr_date = DATE_ADD(curr_date, INTERVAL 1 DAY);
  END WHILE;
END

You can then call this procedure to create days like this:

CALL make_days('2011-01-01','2012-12-31');

Now you can create values for every minute in a given time interval using a query similar to the following:

SELECT YEAR(day) AS year, MONTH(day) AS month, DAYOFMONTH(day) AS day, hour, minute
FROM days, hours, minutes
WHERE CAST(CONCAT(day,' ',hour,':',minute) AS DATETIME) BETWEEN '2011-08-31 22:00' AND '2011-09-01 10:00'
ORDER BY year, month, day, hour, minute

More Related Contents:

  1. PHP DateTime supported formats
  2. Can I call a PHP function in a JavaScript click handler?
  3. How to create a chat program using php and mysqli for chatting [closed]
  4. Just using mysql real escape string [closed]
  5. Many-to-many relationships examples
  6. Can I store images in MySQL [duplicate]
  7. MySQL – This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery
  8. Storing files in database Vs file system
  9. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  10. MySQL Join two tables with comma separated values
  11. How can I Use Prepared Statements in CodeIgniter
  12. iPhone emoticons insert into MySQL but become blank value
  13. Inserting into MySQL from PHP (jQuery/AJAX)
  14. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  15. Selecting all columns that start with XXX using a wildcard?
  16. How to delete mysql row after time passes?
  17. How to display Unicode data with PHP [duplicate]
  18. MySQL Performance – “IN” Clause vs. Equals (=) for a Single Value [duplicate]
  19. Echo menu tree with recursive function
  20. Connect DATABASE Error TYPE: 2002: Permission denied
  21. MySQL skipping first row
  22. How to reverse order output of a MySQL query
  23. Inserting NOW() into Database with CodeIgniter’s Active Record
  24. How to convert excel file into mysql database? [duplicate]
  25. Auto Increment skipping numbers?
  26. PDO bindParam into one statement?
  27. Convert Time String to Decimal Hours PHP [closed]
  28. Is mysql_real_escape_string enough to Anti SQL Injection?
  29. How to make mysqli connect function?
  30. Using PHP to upload file and add the path to MySQL database

Leave a Comment