There is a particular hour, somewhere after midnight, when you do not want the hallway blazing like an operating theater just because someone needs the bathroom. That is the hour night lights earn their keep, quietly, while everyone else in the house is asleep and would like to stay that way.
If you are starting with nothing, go for a plain plug in version with a built in sensor, the kind that only switches on once the room actually gets dark. You want one socket doing the job without you thinking about it again, not a light you have to remember to flick off every morning. Put it low, near skirting board height if you can, so it washes light along the floor rather than up into anyone's eyes.
In an ordinary room, these sit best alongside other low, warm light sources, a dim lamp in a hallway, a strip under a kitchen cabinet, that sort of thing. They make sense next to wood tones and pale walls, where a small amount of light spreads and softens rather than sitting there as one hard dot. They also pair well with anything a child or an older relative needs to find in the dark, a bathroom door, the top of a staircase, the edge of a step that has caught someone's toe before.
Where they do not belong is a bedroom you are trying to keep properly dark for sleep, or right next to a television or monitor, where even a small glow becomes a distraction once the rest of the room goes quiet. Anything with a blue tinted bulb is also worth avoiding near where people actually sleep. That cool light keeps brains more alert than a warm one, which defeats the purpose of the whole exercise.
The difference between a good one and a disappointing one usually comes down to the sensor and the housing. Cheap sensors trigger on and off at dusk for no reason, or stay lit through a sunny afternoon because the plastic over the sensor has gone slightly cloudy. A well made one has a proper light sensitive eye, not just a timer pretending to be one, and a casing that does not yellow after a year near a radiator. LED bulbs that are sealed in rather than a cheap screw fitting tend to last years rather than months, and a diffused cover beats a bare bulb every time, since a bare bulb at ankle height is somehow more glaring than one at eye level.
Small, unglamorous, and one of the few lights in the house that is genuinely working while you are asleep.