The only fun part is when you start a new stage, then it gets boring fast. There is not enough content in any stage, nor is there really any opportunity to customize much after creature stage.
Tribal : Get to dress up your creature and place pre-defined buildings into set spaces.
City: Get to design buildings and vehicles, city shape; size and lot positions predetermined.
Space: Design a spaceship, place 3 colonies per planet, 3 kinds of determined fauna/flora per planet.
After running through 20 or so files, which only takes about 3 hours per file, you get bored fast.
Cell: 10m - 20m
Creature: 1h30 - 1h50
Tribal: 30m - 50m
City: 5m - 10m
Space: Infinite; endgame, 10h - 30h
Spore is fun to use when you are trapped on a plane or in a car and have no internet access and a computer, but I would not choose to play spore over another game.