I always thought that the (real-time) week and day/night progression in pokÚmon gold/silver was a cool mechanic. For those of you who never played it, some NPCs would only show up at certain times on certain days, some pokÚmon would only appear at night, the evolution of some pokÚmon would depend on when did they evolve, etc...
Also, TimeSplitters: Future Perfect was pretty damn entertaining as an FPS, and managed to tell a story through multiple time periods (1920s, 1970s, 22nd century and so on). There were a few sections where the main character was assisted by his future selves, and then a short while later you'd be that future self assisting you in the past.
I was going to mention that. Haven't played it myself yet either, but intrigued to see how it differs on various playthroughs.
Originally Posted by Tikey
Grim Fandango's narrative played out over years, as far as I recall.
There are a number of games (oblivion, far cry 2, the witcher etc) where the time of day actively changes the type of encounters you will have.
I'm not sure this is exactly what you were going for, but I suppose The Sims is rather interesting in the fact that you can play through not just your characters entire life cycles (as they age and ultimately die), but also their subsequent generations. I can't remember which Sims game first included the whole life cycle/generations mechanic though....2 or 3?