I'll try give you a few ways to look at this:
1 production on turn 10 is worth a lot more than 1 production on turn 100. So the value of your small steady income steadily diminishes.
So if you have to choose between 1 production per turn, or getting say 50 production at once, you do not need just 50 turns time for those choises to be equal. In fact, since inflation over 50 turns is really big, the value of that small trickle will probably never add up to 50. (its like 1 + 0.95+0.905+0.86+0.817+....)
Those who advocate aggressive harvesting do so from the viewpoint that efficiency = winning earlier. They will win their games usually between turn 150 and 200. Say you are going to win a domination victory at turn 180. Any troops you build after turn 160 will never reach the front lines, and thus your production is worth nothing after turn 160. At turn 120, your small trickle can only provide you 40 more production of value. (and that is not even counting the inflation explained above) If at turn 120 you can gain more than 40 by harvesting, that is obviously a good idea. Add the inflation story to that, and you will want to harvest even earlier.
If you think you can't win before turn 200 and thus it's probably not a good idea for you to harvest, you sort of make it a self-fulfilling prophecy. It is however understandable that while those who only live to win as fast as possible see no counter to this argumentation and rightfully harvest everything, others feel it's a choise of playstyle. Civ is after all also a builder game, and we like it partly because we want to build a beautifull and powerful empire, not to deplete everything as fast as we can and win with a shriveled sort of empire that seems to be winning on it's last breath. (it is after all not only harvesting this logic goes for, it is also to be considered when doing other longer term investments. You don't want to be building mines, factories, powerplants etc if your game is not going to last long enough to make them worthy, so you don't build any of that crap, you just harvest and cram out units asap)
It's a good thing they make the value of the harvesting grow over time. In the very early game, there is virtually no value to gain, because 1 worker action costs about 20 production and your harvest doesn't really give you much more than 20 production. As time goes on, the harvest becomes more valuable. By the time you get to feudalism, your worker action costs less, and the harvest provides a lot more, so now it has become very valuable. When exactly you should harvest is a complicated matter, but @ feudalism is probably a good baseline since that is both where your harvests just jumped up in value and it also is, if you play aggressively and win fast, between 50 and 100 turns before the end of the game. Stuff that is very valuable for harvesting you may want to harvest earlier. Stuff that is in your way for districts you probably want to harvest super early. (district placement should absolutely be prioritized over preserving harvestable resources) If you want a space victory, you may well want to keep stuff around one or 2 cities till the end so you can harvest it to build space parts. And many more considerations can go into what you will harvest when. But at some point you should harvest everything, winning the game with unharvested resources around is like dying with a big fat bank account.
Yes, i also thought about inflaition, but mostly it's my playstyle them. A always play till the end. Late game is the most interesting for me. Especially after addition of the future era.