If you already worship a god, using a faded altar will most likely result in you angering your former god when you convert. However, the conversion allowances offered by the good gods still apply as normal, and it is possible (though unlikely) that the randomly selected god will be the one you currently worship, in which case the only effect of your actions is the immediate gain of 20 piety.
If you want a good chance of winning your game, praying at a faded altar is nearly always a bad idea. For any given character, there are usually two or three gods that will make life very difficult. A caster will not do well worshipping Trog or Pakellas, a troll worshipping Gozag will likely starve, and so on.
However, if you're optimizing for fun and not too worried about the possibility of dying or being severely inconvenienced, faded altars can provide you with an early power boost or at least create a very interesting situation to deal with.
Faded altars were added in 0.17. They were called "ecumenical" altars during development.