Posted By

ktrout on 01/03/16


Tagged

mysql calendar generator


Versions (?)

mysql calendar table


 / Published in: SQL
 

creates a minimal calendar table

  1. DROP TABLE IF EXISTS calendar_t;
  2. CREATE TABLE calendar_t (
  3. id integer PRIMARY KEY AUTO_INCREMENT NOT NULL,
  4. `date` date NOT NULL,
  5. day varchar(9) NOT NULL,
  6. month varchar(13) NOT NULL,
  7. `year` integer NOT NULL
  8. );
  9.  
  10. DROP VIEW IF EXISTS digits_v;
  11. CREATE VIEW digits_v
  12. AS
  13. SELECT 0 AS n
  14. union ALL
  15. SELECT 1
  16. union ALL
  17. SELECT 2
  18. union ALL
  19. SELECT 3
  20. union ALL
  21. SELECT 4
  22. union ALL
  23. SELECT 5
  24. union ALL
  25. SELECT 6
  26. union ALL
  27. SELECT 7
  28. union ALL
  29. SELECT 8
  30. union ALL
  31. SELECT 9
  32. ;
  33.  
  34. INSERT INTO calendar_t
  35. ( `date`, day, month, `year` )
  36. SELECT
  37. date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day) AS `date`,
  38. dayname(date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)) AS day,
  39. monthname(date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)) AS month,
  40. year(date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)) AS `year`
  41. FROM
  42. digits_v a2
  43. CROSS JOIN digits_v a1
  44. CROSS JOIN digits_v a0
  45. ORDER BY date_add('2015-01-01', interval 100*a2.n + 10*a1.n + a0.n day)
  46. ;

Report this snippet  

You need to login to post a comment.