My favorite all time game would have to be Super Mario Bros 3. I grew up with it, and can easily play it every once in a while. I don't know about having someone wake me up in the middle of the night to play, but I get what you're saying.
Other games that come to mind for me are; Call of Duty 4, Metal gear Solid 3, Alan Wake, Batman Arkham series, and many more.