I didn't mind the Fallout 1 timer, it was always on the forefront of my mind but wasn't impossible to deal with either. What got ME in that game [spoiler warning] was when you had an option to postpone it by talking to a group who, IIRC, end up raiding the vault and killing them anyway. Or something to that effect, it's been a while.
That being said, I would prefer not to have "total failure" timers, especially if like in Fallout 1 and "You've failed to get the water chip in time, so everyone in the Vault died."
I'd like to see more consequential timers, as in "this much time has passed, so people in the Vault have STARTED dying," or "...clearly the Vault is no longer viable so you need to convince the Overseer* to clear as many people out of the Vault as possible," or something. Give us lesser alternatives, don't just say "Game Over." Same with games like Grand Theft Auto, come up with a solution if the mission goes off the rails time-wise. Allow for more/different story beats where someone fails at something, with the option to reload/restart if someone doesn't WANT to move forward with the failure option.
(* - Was that his name? I forget offhand.)
On the flipside, I don't like fake timers in games. That is, quests where the impression is "You just missed them" or "If only you'd been here sooner," but a reload and faster response (or even early arrival) results in the same outcome. I get that sometimes this is to extend a quest or further the story, but again, don't put the burden of implied timing failure on the player if in fact they had no choice but to fail.
Basically I think timers, whether real or arbitrary, are a way of forcing players down a particular narrative path and pacing that is, to me, at odds with the general concept of roleplaying. Sure, have consequences for not accomplishing X within a reasonable timeframe, but then let the player(s) work with that, unless there's a really good reason not to. (If for instance, you have only so much time to get out of Pompeii before Vesuvius erupts, or to get off Earth before it's demolished for a hyperspace bypass, then I get not having an alternative.)