CODEWITHSUNDEEP
mysql
Marcel Hanselmann
Marcel Hanselmann

Reputation: 21

How to get a list of years from start year and end year

I have a mysql table with:

id --- name --- start_year --- end_year

1 --- george --- 2005 --- 2009

How do I get with a mysql query the year between this to years like:

2005
2006
2007
2008
2009

?

Upvotes: 1

Views: 421

Answers (1)

Caius Jard
Caius Jard

Reputation: 74710

Using any one of the answers in here that you like the look of, generate a list of numbers from eg 1000 to 4000

When you do

SELECT * FROM rowgen

You should see a list of numbers:

num
1000
1001
1002
...
3999
4000

Or whatever range will be acceptable for your dates. Maybe 2000 to 2050 will do. Anyway... then you just join the list to your table ON rowgen.num BETWEEN start_year AND end_year

SELECT *
FROM
  yourtable t
  INNER JOIN
  rowgen r
  ON r.num BETWEEN t.start_year AND t.end_year

You'll get:

1, George, 2005, 2009, 2005
1, George, 2005, 2009, 2006
1, George, 2005, 2009, 2007
1, George, 2005, 2009, 2008
1, George, 2005, 2009, 2009

The last column being the generated row numbers

Upvotes: 1

Related Questions