The "Quiet Hours law" is part of environmental protection laws: it regulates noise immissions.
In general, what is allowed differs quite a bit depending on zoning and day vs. night.
E.g. in a purely industrial area outdoor noise immission may be up to 70 dB(A) any time of the day. Whereas in hospital or nursing home zones only 45 dB(A) during the day and 35 dB(A) during the night are allowed.
It also depends on the type of noise source, e.g. construction noise has a longer night (20:00 - 7:00 compared to 22:00 - 6:00 for normal "commercial" noise sources) and some penalty for early/late day noise, commercial noise has a penalty* for early/late daytime and sundays; sports noise has day, night and quiet hours and they differ working days vs. sundays and holidays. There are no legal limits on noise emitted by children (incl. kindergarden, playgrounds), but neighborhood noise (party) does have restrictions, and machine-emitted noise has further restrictions.
* penalties: some of these regulations use weighted sound pressure levels that upweight certain particularly disturbing characteristics of the noise (information content of noise, particular tones sticking out, impulse-type [sudden] noise, ...).
Festivals and events are covered by the
"recreational noise directive" (Freizeitlärmrichtlinie, state law)
- puts emphasis on communal planning as in don't put hospital and fair grounds or outdoor recreational swimming pool next to each other, keep some distance between the local skateboard ramp and residential housing.
Also, the town market place (where fairs are held) would be an urban zone (or "core" zone) which has higher noise limits than a purely residential zone.
Exceptions can be granted for rare (up to 18 days/year) events and festivals that are tightly bound to a particular location ("hohe Standortgebundenheit", e.g. Kieler Woche, Oktoberfest or the annual village fair, but also "moving" festivals like the Hessentag) or socially adequate and accepted.
These exceptions can include moving the night hours up to 2 hours (usually only for Friday or Saturday evenings or evenings before public holidays).
The higher the (expected) noise level and the more often, the more strictly the exception has to be evaluated. Also, not more than 2 weekends in a row are supposed to be subject to such rare events, and the neighboring residents have to be told (usually >= 2 weeks in advance).
There are also technical measures such as focusing sound on the festival area and making sure loud speakers do not emit sound in other directions more than necessary.
"sports grounds noise directive" (Sportanlagenlärmschutzverordnung)
- again, higher immission levels are allowed for rare events, rare being up to 18 days/year
- exceptions can be granted for sports events of major national or international importance, so when the soccer world cup came to Germany, residents had to put up with that.
sometimes exceptions are granted by directive, e.g. public viewing during the 2018 soccer world cup
Pubs are commercial businesses and subject to the TA Lärm, but sometimes somewhat less strict rules be used (e.g. see here for NRW). A pub thus cannot be in a purely residential zone: where you have a pub, you are at least in a general residential zone which has higher noise limits.
Diskotheken (night clubs) are often in industrial zones.
A private party is so-called neighborhood noise (Nachbarschaftslärm) and imission here is governed by state law, plus possibly communal bylaws and last but not least binding clauses in e.g. the rent contract.
If there isn't any particular law (AFAIK, e.g. Hesse does not have a state law on this), there's the catch-all §117 OWiG on Inadmissable Noise:
(1) Whoever, without a justified reason, or to an inadmissible extent, or to an extent avoidable under the circumstances, causes noise which is suitable to create considerable disturbance to the general public or to the neighbourhood, or to inflict harm upon the health of another, shall be deemed to have committed a regulatory offence.
A common rule of thumb would be that between 22:00 - 6:00 and on Sundays or holidays you shouldn't do anything that can be heard outside your flat (Zimmerlautstärke).
A typical practical approach is to tell your neighbours that you'll have a party and will they please excuse if there's some noise? Bonus points if you invite them. This doesn't change anything about legal noise levels, but it usually does the trick in terms of no neighbours complaining/not disturbing them.
- There are so-called silent holidays (again state law, e.g. NRW) with particularly heavy restrictions (no sports events, no markets/fairs/circus, no recreational events, ...)