Yes, that's an issue which many of us have had. It's mostly a saddle and shorts issue in my experience. Exact saddle shape, both in profile and plan view are important, as is exact amount of padding or lack thereof. The thickness and shape of the pad in one's cycling shorts is also critical. Plus some saddles are more comfortable with one pad configuration, while a different saddle might be better with a different pair of shorts. Plus in my experience, what is the most comfortable saddle may change with time as one's butt changes shape and muscularity.
Your observations about sitting versus standing as the miles add up are accurate in my experience. I do best with Castelli Endurance shorts. Others do better with Assos bibs. I like my Selle Italia MAN saddle more than anything else I've tried. I never could do long distance on an unpadded leather saddle, though many like them. Look around at other brevet riders who look like you. What are they using?
Another butt problem which is not spoken about enough is peroneal numbness, most noticeably penile numbness. That is an absolute no-no. Some shorts/saddle combinations can cause that, others not at all, for the same person and length of ride.
The scientific explanation for sore butt on long rides is oxygen deprivation of the tissues. They don't like that. And that's why standing helps - it lets in blood flow and thus oxygen. One tries to find the combination which is less likely to cut off the blood supply to butt tissues. It's always going to be reduced and to which they can adapt, but not too much reduced.
__________________
Results matter