with water, I think about 1.5 years (old enough to have a little restraint about not spilling on purpose). Supervised drinking at that age in case they lost that restraint.
At 2.5 we still use sippies in the car and with juice. We're starting to convert to sports bottles and have used flip-top cups with straws for a long time too. Water is always in small open cups and not supervised. Not the norm apparantly. Almost 3yos in her class had never drunk from open cups before. By the end of the year, I had them all doing it. Moms got a little horrified that my physically small and younger kid could handle a dixie cup without spills. Most of them had just never given their kids the chance. And with small amounts in small cups on hard floors or kitchen it's easy to clean if they spill. Even easier when it's water. If I had wall to wall carpet I'd just be on an only-water kick.