When is the Hamiltonian of a system not equal to its total energy?

In an ideal, holonomic and monogenic system (the usual one in classical mechanics), Hamiltonian equals total energy when and only when both the constraint and Lagrangian are time-independent and generalized potential is absent.

So the condition for Hamiltonian equaling energy is quite stringent. Dan's example is one in which Lagrangian depends on time. A more frequent example would be the Hamiltonian for charged particles in electromagnetic field $$H=\frac{\left(\vec{P}-q\vec{A}\right)^2}{2m}+q\varphi$$ The first part equals kinetic energy($\vec{P}$ is canonical, not mechanical momentum), but the second part IS NOT necessarily potential energy, as in general $\varphi$ can be changed arbitrarily with a gauge.

The Hamiltonian is in general not equal to the energy when the coordinates explicitly depend on time. For example, we can take the system of a bead of mass $m$ confined to a circular ring of radius $R$. If we define the $0$ for the angle $\theta$ to be the bottom of the ring, the Lagrangian $$L=\frac{mR^2\dot{\theta}^2}{2}-mgR(1-\cos{(\theta)}).$$ The conjugate momentum $$p_{\theta}=\frac{\partial L}{\partial \dot{q}}=mR^2\dot{\theta}.$$ And the Hamiltonian $$H=\frac{p_{\theta}^{2}}{2mR^2}+mgR(1-\cos{\theta}), $$ which is equal to the energy.

However, if we define the $0$ for theta to be moving around the ring with an angular speed $\omega$, then the Lagrangian $$L=\frac{mR^2(\dot{\theta}-\omega)^2}{2}-mgR(1-\cos{(\theta-\omega t)}). $$

The conjugate momentum $$p_{\theta}=\frac{\partial L}{\partial \dot{q}}=mR^2\dot{\theta}-mR^2 \omega.$$

And the Hamiltonian $$H=\frac{p_{\theta}^{2}}{2mR^2}+p_{\theta}\omega+mgR(1-\cos(\theta-\omega t)), $$ which is not equal to the energy (in terms of $\dot{\theta}$ it has an explicit dependence on $\omega$).

Goldstein's Classical Mechanics (2nd Ed.) pg. 349, section 8.2 on cyclic coordinates and conservation theorems' has a good discussion on this. In his words:

The identification of H as a constant of the motion and as the total energy 
are two separate matters.  The conditions sufficient for one are not 
enough for the other.  

He then goes on to provide an example of a 1-d system in which he chooses two different generalized coordinate systems. For the first choice, H is the total energy while for the second choice H ends up being just a conserved quantity and NOT the total energy of the system.

Check it out. It's a very nice example.