What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

by

When you need something like that on server side, you usually create a table which contains all possible dates between two points in time, and then left join this table with query results. Something like this:

create procedure sp1(d1 date, d2 date)
  declare d datetime;

  create temporary table foo (d date not null);

  set d = d1
  while d <= d2 do
    insert into foo (d) values (d)
    set d = date_add(d, interval 1 day)
  end while

  select foo.d, count(date)
  from foo left join table on foo.d = table.date
  group by foo.d order by foo.d asc;

  drop temporary table foo;
end procedure

In this particular case it would be better to put a little check on the client side, if current date is not previos+1, put some addition strings.

More Related Contents:

  1. How to get difference between two dates in months using MySQL query?
  2. Count days between two dates, excluding weekends (MySQL only)
  3. With MySQL, how can I generate a column containing the record index in a table?
  4. Using column alias in WHERE clause of MySQL query produces an error
  5. Which is faster: multiple single INSERTs or one multiple-row INSERT?
  6. On duplicate key ignore? [duplicate]
  7. How to convert timestamp to datetime in MySQL?
  8. MySQL varchar index length
  9. List of Stored Procedures/Functions Mysql Command Line
  10. Best practices for SQL varchar column length [closed]
  11. How to design a multi tenant mysql database
  12. What are differences between INSERT and UPDATE in MySQL?
  13. MySQL dynamic cross tab
  14. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  15. How to disable index in innodb
  16. How to delete duplicate rows from a MySQL table
  17. mysql: why comparing a ‘string’ to 0 gives true?
  18. SQL query to check if a name begins and ends with a vowel
  19. What is the best way to store media files on a database?
  20. mySQL Error 1040: Too Many Connection
  21. base64 encode in MySQL
  22. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysql.sock’ (2)
  23. PID error on mysql.server start?
  24. remove all numeric characters from column mysql
  25. INNER JOIN Results from Select Statement using Doctrine QueryBuilder
  26. How can a query multiply 2 cell for each row MySQL?
  27. Rails select random record
  28. mysql UPDATE statement – overhead for same values?
  29. Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’
  30. What is the jdbc driver “org.gjt.mm.mysql.Driver” for?

Leave a Comment