It could be a few things.
I've most commonly seen it refer to a physician who is taking consults (short for "consultations" I suppose) for his specialty, and I'm guessing this is the one you are referring to. For example, when somebody is admitted to the hospital to have their gall bladder removed, they may be on the surgery service, meaning that the surgery team is the primary team responsible for their care. If something comes up that the surgeons need help with, say, difficult to control diabetes, they may then "consult (internal) medicine," in which case whoever is handling (internal) medicine consults that day will come, see the patient, and answer the question the primary team is asking (i.e.-"how do we manage this patient's diabetes?"). I'll leave questions about hours, pay, work structure, etc to those who know more than me about this.
At the Mayo Clinic a "consultant" is the attending physician, AKA "attending." This is the fully trained doctor who supervises residents or sees patients by himself/herself without residents/fellows/others. It was explained to me that this terminology is preferred because it reinforces the idea that the residents are in charge and the attending is just there to consult with.
And of course, a "consultant" could be somebody that the hospital has hired to do something - like an IT consultant who comes in to install a new electronic medical record system.
