Yeah, I'm definitely interested in keeping as much of the DST stuff outside my code as possible. I guess my concern is that I think there are other places where this database is being used in my codebase that may rely on the database time setting being UTC (or really, GMT, though I don't think there's a difference for these purposes). It would be best if all of my application's code declared its intentions about the time zone of the database connection before running its query, but I don't think that's a feasible change to make right now. That's what's motivating my pursuit of finding a way to write these queries without changing this setting, through appropriate casting and such.
As to "other places" in your (or others) code, provided that you are storing and manipulating your data as timestamp WITH time zone (point in time), it can be inserted, reported, displayed, calculated, etc. in whatever time zone any client or piece of code prefers. But when displaying data around DST changes, fall-back in particular, the offset must be included in your output to disambiguate things like 01:30:00-07 from 01:30:00-08. Both are 1:30am but are one hour apart.