Does it take a while to get over the effects of twilight anesthesia for a colonoscopy?

Depends on type. If the anesthetic is given by an anesthesiologist they would usually use a very short acting anesthetic agent. If the sedation is done by the GI doctor they tend to use a longer lasting agent. In either case you should be clear headed after a few hours.
Sedation. Typical medications used for colonoscopy are Midazolam (versed), fentanyl (sublimaze) and Propofol (diprivan). While other medications are used, these are usually the most common. All have very short half-lives and clinical effects are usually gone within 24 hours. I am not aware of longer term effects if these are similar to the medications you received during your procedure.
Mac anesthesia . Without any other health issues related to the metabolism of the medications, you should generally feel back to normal within a few hours depending on the agents that are used and certainly after 24 hours.