I have three ideal game lengths.
I love love love 2-4 hour games I can finish in a single session. I basically need one of those now and then to feel I got a complete experience in between all the longer stuff. Think stuff like Journey, Life is Strange episodes, Never Alone etc.
I really like 8-11 hour games I can finish in either one long all day session, two evenings, or a weekend. Uncharted trilogy etc.
For longer games my attention span really struggles if it's more than 20-25 hours. Unless it's incredibly compelling and I have the urge to stuick with it, which is rare. Mass Effect was perfect, you can main story it in 20 hours, with 43 hours if you harvest every side scrap of data. Sleeping Dogs for the open world was just right, with 100% taking 45 then 35 hours on my two runs, but you can main story it in 15 if you're impatient.
For me, anything longer than 25 hours and I often get distracted at the 12 hour mark. Especially JRPGs where it is ALWAYS at 12 hours. I go to fire it up again, but instead of thinking "I'll play alllllll day and finish this" I think "I could play this all day (again) and still not finish this" and that's daunting AF. It can even be a good game that I'm enjoying, but if I can play it from morning to night for a full weekend and still not finish it? Yeah, I'm going to be looking for fresh distractions. I think part of it is that I'm often very story orientated in games, and anything longer than 25 hours and the narrative just isn't going to be dense enough or compelling enough to sustain it.
That said I'm not against larger games you can forever dip into. I still have a Just Cause 2 save file that once a year I fire up and lose a whole day to as I slowly crawl towards 100% while I clean dat map.