For family travels, I use RV parks and campgrounds as you describe, for overnight. No, there is no special rate for overnight only, per night is the same as per day. Same as a motel, rate doesn't matter whether you come in at midnight and sleep four hours, or come in at 4 PM and check out at 10 AM, or continuing day to day.
For this, I've used KOAs, USA Corps of Engineers parks, state parks campgrounds, Good Sam RV parks, unaffiliated RV parks as needed. Traveling with wife and daughters, a parking spot at a rest area was out of the question, they want a bath house with a shower powerful enough to rinse the shampoo from a thick head of hair.
On my own, just looking for a place to sleep, I might consider a rest area or parking lot. Not about the money, just the time savings not hooking up.
Many public campgrounds, at least in the middle of the country, will close the gates overnight for security. Sometimes the campground, sometimes the whole park. Typically 10 PM - 6 AM, but it varies.
Tom Test
Itasca Spirit 29B