And of the Command and Conquer games. The missions are always hard, and there are so many. Not to mention the skirmish mode, where you can go against lots of AI players at once.
I would also recommend the Battlefield games. While they are mainly designed for multiplayer playing, their single-player mode works the same, but instead of the other players being other people around the globe, they are controlled by the AI. It can get a bit repetitive after a while though, especially when you learn the way the AI likes to attack.
Sim City 4 + Rush Hour expansion is great if you like games like Sim City. I often play it, and before I know it, 4 hours have passed.
If you like motor sport, Toca Race Driver 2 is an awesome game, and GP4 can take a good 3 hours of your life at a time (assuming you do the full 50+ laps).
I had 56k for a long time, so I have played many of these games prior to having the ability for multi-player. Even though I now have DSL, I still prefer to play most of them in their single player modes.