To answer your question on a dual-core PC for 2005-2009, grab an E8400 and never look back. It's very cheap, plenty fast (it was the go-to gaming CPU back in 2008-2009) and doesn't consume a whole lot of power and overclocks like a dream. Pair it with a decent P35 or P45 board and you're good to go. ASUS P5K was my mainboard at the time and I think it is my favorite board of all time, zero issues, very stable and completely dependable board at the time. In fact, if you wish to go even lower power, the E8400 was a great candidate for undervolting. You can also get the faster E8600, I seem to remember these being only slightly more expensive, but I haven't checked in a while.
I would avoid anything AM2/AM2+/AM3. The Phenom II line had a lot of great SKUs (I am especially partial to the X3 720) which offered great value with more cores for your buck at the expense of power consumption and per-core performance, however seeing as you will use this system solely for gaming, the timeframe you have given is filled with games that barely took full advantage of two cores, as such anything more than two cores is rather useless, unless of course you move further in time in which case you can get more cores and better single core performance. I would avoid Nehalem i7, they were real beasts but they are still somewhat silly expensive (especially the motherboards). They were not entirely unlike current HEDT systems, aimed at more enthusiast buyers with their triple channel memory support and other exotic features (and yet i7 920 was less than 300$!). Instead, you may consider the socket 1156 CPUs, the i5 750 especially. These are not as expensive as s1366 and still offer a nice boost over Core 2 Duo, although IMO E8400 will be enough for your needs.
As for when the need for more cores began, I agree with what some people above already said, 2013-2014 was essentially the last hurrah. I remember arguing with people online on whether or not i3 with its hyperthreading was still viable. Good times 😀