moonlights are usually very weak LED lights that can be added to a canopy or retrofitted to a hood. I remember when they became en vogue with reef keepers about five years ago over at
www.reefcentral.com.
The theory is, the moon does reflect a certain amount of sunlight (called albedo, 0.39 to be exact for you vangelis fans) at night, so reefs in the wild are never truly completely dark. Some weak blue light does reach reefs at night. Some reef keepers believe that using them mimics a more natural setting for their corals, inverts and fish, and could be a trigger for noctournal animals, spawning behavior, etc.
IMHO, I don't find them to be necessary, nor would I go out of my way to purchase some.
For diurnal fish, I wouldn't want to run lights 24/7 in any fish room or on any aquarium. But that doesn't mean you have to cut off all lights in the room. Some nervous fish do better when there's some ambient light in the room (discus, for example). Just enough light for them to see, but weak enough to trigger the sleep response. A child's nightlight in the room would be perfect.
For the nocturnal fish, if there's enough ambient light in the room for your eyes to adjust to it after a minute, then it should be enough for noctornal fish to function just fine.
And yes, ambient red light isn't seen by most fish/inverts. Water filters out the red color spectrums beyond eight feet deep. It's one reason why so many deep water sea creatures (such as the vampire squid) are bright red. Just cover a flashlight with red cellophane and a rubber band and you can peer into your tanks at night. Some animals will notice the motion of the light beam and hide, but if you put the flash light shining in one spot and don't move it around (casting a red glow of light into the tank), nocturnal fish/inverts won't see it.
Call me fishmael...