generate_series() equivalent in MySQL

by

This is how I do it. It creates a range of dates from 2011-01-01 to 2011-12-31:

select 
    date_format(
        adddate('2011-1-1', @num:=@num+1), 
        '%Y-%m-%d'
    ) date
from 
    any_table,    
    (select @num:=-1) num
limit 
    365

-- use limit 366 for leap years if you're putting this in production

The only requirement is that the number of rows in any_table should be greater or equal to the size of the needed range (>= 365 rows in this example). You will most likely use this as a subquery of your whole query, so in your case any_table can be one of the tables you use in that query.

More Related Contents:

  1. INSERT INTO SELECT statement with more columns which is not in another table
  2. SQL Print the name of all Employees together with the name of their supervisor
  3. MySQL how to fill missing dates in range?
  4. Left Outer Join doesn’t return all rows from my left table?
  5. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  6. 1052: Column ‘id’ in field list is ambiguous
  7. How to split the name string in mysql?
  8. Check if MySQL table exists without using “select from” syntax?
  9. sql join two table
  10. Compare dates in MySQL
  11. Cast int to varchar
  12. SQL query of multiple values in one cell
  13. Import SQL file into mysql
  14. MySQL SELECT last few days?
  15. “where 1=1” statement [duplicate]
  16. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  17. Split a string and loop through values in MySQL stored procedure
  18. how to cast the hexadecimal to varchar(datetime)?
  19. MySQL Select Query – Get only first 10 characters of a value
  20. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  21. How to get a list of months between two dates in mysql
  22. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  23. Postgres LEFT JOIN with WHERE condition
  24. How to format number with “.” as thousands separator and “,” as decimal separator?
  25. How to check if field is null or empty in MySQL?
  26. mysql select id and name from other table and join query
  27. How do you force mysql LIKE to be case sensitive? [duplicate]
  28. #1060 – Duplicate column name ‘id’
  29. In what order are MySQL JOINs evaluated?
  30. Update multiple rows with multiple ‘where’ clauses for each individual row

Leave a Comment