Demand is low at night, but many types of power plants are not easily/efficiently throttled down for lower supply (e.g.: nuclear, wind). So it makes sense to use the extra supply to pump water up.
They generate the energy probably all of the time. At night, however, fewer customers are using and so they can store the excess. That stored energy can then smooth out large changes in the daytime operating hours, or it can just supplement the supply as necessary.
At least in the case of https://www.electricmountain.co.uk/Dinorwig-Power-Station it provides near-instantaneous peak power (especially at the top of the hour when everyone turns on their kettle for tea), and is recharged through the early am via nuclear and other power plants that can't be shut down that quickly.