Generating a series of dates [duplicate]

by

if you’re in a situation like me where creating temporary tables is prohibited, and setting variables is also not allowed, but you want to generate a list of dates in a specific period, say current year to do some aggregation, use this

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2017-01-01' and '2017-12-31'

More Related Contents:

  1. how to get result form mysql query in specific ids [closed]
  2. mysql error cant fix the error [duplicate]
  3. Why can’t this entry be added because of Foreign Key error?
  4. MySQL pivot row into dynamic number of columns
  5. How should I resolve –secure-file-priv in MySQL?
  6. node-mysql multiple statements in one query
  7. MySQL server startup error ‘The server quit without updating PID file’
  8. MySQL AUTO_INCREMENT does not ROLLBACK
  9. Storing JSON in database vs. having a new column for each key
  10. How to handle fragmentation of auto_increment ID column in MySQL
  11. Groupwise maximum
  12. What effects does using a binary collation have?
  13. Are dynamic mysql queries with sql escaping just as secure as prepared statements?
  14. MySQL automatically cast/convert a string to a number?
  15. How to select rows that have current day’s timestamp?
  16. What’s the best database structure to keep multilingual data? [duplicate]
  17. LEFT JOIN only first row
  18. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  19. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  20. MySQL connection over SSH tunnel – how to specify other MySQL server?
  21. Storing IPv6 Addresses in MySQL
  22. Insert/ Update random date in MySQL
  23. How to determine which database is selected
  24. How to remove constraints from my MySQL table?
  25. Get total hours worked in a day mysql
  26. WooCommerce: Finding the products in database
  27. How can I “select *” from a table in MySQL but omit certain columns?
  28. For homebrew mysql installs, where’s my.cnf?
  29. MySQL Select Multiple VALUES
  30. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

Leave a Comment