To me, it’s less about the time since the console and more about what the average game on the console looked like. While I personally range from “don’t mind” to “quite enjoy” older graphics including pixel art and low poly 3D, the average N64 honestly looked pretty awful in comparison to modern 3D graphics. In Super Mario 64, Bob Omb Battlefield was 2,352 polygons total, compared to an average of 60,000 per level in Super Mario Sunshine. Not to mention all of the additional effects that the GC was able to pull off on top of just raw polygon counts.
The PS1 to PS2 transition had a similar leap in graphical fidelity, though the last major PS1 titles certainly looked a lot closer to what the early PS2 titles did at the time. While I think Final Fantasy 9 looks amazing and it sometimes surprises me that it’s a PS1 title, I think Final Fantasy 7 looked closer (than at least 9 does) to what the average PS1 title looked like graphic wise and the difference in quality between it and Final Fantasy 10 is an incredible leap.
I guess you could also make the argument that retro games are the ones that were primarily designed for being played on a CRT in which the sixth generation of consoles (GameCube, PS2, original Xbox) all would fall under compared to the next generation that at least with the PS3 and Xbox 360 both largely tried to push a new “HD era”. But personally I still see the leap between 5th and 6th generation to be probably the biggest leap in graphical fidelity we’ve ever had and to me that makes it the end cap for the retro console.
Though I do know a bit of that is because of the jump to 3D did kinda take us back a few steps in graphics…
On that note, perhaps it would make more sense to look at when a console “dies off” than when it first released? In that sense, the PS4 is still a pretty recent console arguably still modern.