Most websites allow an e-mail address to be used only for one account which makes sense because most of the time, users only need one account. Therefore, an unique e-mail address is required.
That being said, once the user has registered but only needs to confirm his e-mail address, you want to insert in the database the e-mail address of the user to not allow someone else or the very same person to register again. If you do not handle e-mail confirmation expiration, someone could register with someone else's e-mail address and never confirm it which would lock the e-mail of the legitimate user if he ever wants to register to your website.
If the user hasn't confirmed his e-mail address in the given length of time, you want to make it available again, in case it wasn't really his address or if he wants to register again later on. Consider the case where a user entered the wrong e-mail address by a mistake.
Now for the right length of time, I'd say that it depends on the type of website it is. I don't see the point of allowing more than a few hours because the user should be able to quickly access his e-mail address if he was able to register to your website. Consider the case where a user forgot his e-mail address password and can't access it. He might need to go through some steps to get his password back which could take a while. However, would it really do harm if he has to register again? Once again, it depends on the website.