YSK: A car takes uses about seven seconds worth of fuel to start up. If you are going to idle for a period of time longer than that, you'll save fuel by turning your engine off.
Why YSK: People seem to, on average, think that a car takes a lot of fuel to start up. In reality, it takes on the order of a few millilitres of fuel to start an engine. That means if your car isn't equipped with an automatic start/stop system to stop your engine instead of idling, it saves fuel to turn off your engine and start it back up when you need it.
Caveat: air conditioning and radio might not work with the engine turned off.
Scenarios where this might be useful include waiting for trains to pass at rail crossings, waiting for food at drive-throughs, dropping off or picking people up on the side of the road when they need to load stuff, etc. May not be a good idea to use this while waiting at a red light because starting the engine does take time which would annoy drivers behind you when the light turns green.
Some cars are equipped with systems that will automatically stop the engine when you are idling for a while (e.g. waiting for a red light). If yours is, then manually turning off your engine will probably result in reduced fuel savings compared to just relying on the car to do it for you.
It's not necessarily an issue of fuel, but the overall wear on the components and engine when you start a car. A starter motor only has so many "starts" it can do before dying. The battery too.
Starter motors have gotten a lot better since the "bad old days" and engines start more smoothly thanks to fuel injection and computer control systems, so manufacturers have decided that it's ok to start/stop engines as needed, but the reason for not doing it was never a matter of fuel savings.