0

I'm attempting to use the Date Generation Example to generate my own date range but I can't get the query to run. The query fails and Workbench shows an error indicating my syntax is invalid.

Am I overlooking/misunderstanding something about how this can be used? I can't use stored procedures so I hope this isn't limited to being used in that context.

use mydb;

WITH RECURSIVE dates (date) AS
(
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE DAYOFWEEK(date) = 1 AND
    date >= DATE_SUB(NOW(), INTERVAL 1 YEAR);
)
SELECT * FROM dates;

enter image description here

1

Recursive CTE implemented in the MySQL 8+ requires the next syntax:

WITH RECURSIVE tbl (a,b,c) AS 
(
  SELECT ...  -- initial portion of rows
  UNION ALL
  SELECT ...  -- recursively added rows
)
SELECT * 
  FROM tbl
   ...
;

Refer to the documentation/primers for the proper recursive CTE using.

  • That works, but is it possible for me to add additional criteria to the "recursively added rows" - e.g. - SELECT value, date FROM daily_stats WHERE date + INTERVAL 1 DAY <= NOW() AND daily_stats.name = 'user-count'? When I do that it says "unknown column name". – Webnet Jan 25 '19 at 4:05
  • @Webnet Can you explain what exactly you want to get in general? – Kondybas Jan 25 '19 at 9:36
  • Nevermind, I had a simple mistake in my query. This answer is correct, thank you! – Webnet Jan 26 '19 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.