In Postgres terminology, all functions have a volatility classification: IMMUTABLE (input X will always return y forever), STABLE (input x will always return y for the current statement) and VOLATILE (input x can return anything). That classification impacts how the query gets planned and optimized. All the dbs I know have some version of this.

now() should be STABLE in pretty much every db, but not every db gracefully handles STABLE function calls in predicates.

You probably know all this but figured I'd share the database jargon for everyone else.

That seems like it would cause a lot of problems, other than the optimization.

That's how my colleague described it to me, seems he misspoke or I misremembered.

I checked the documentation and it says now() is evaluated at most once per request.

The point was now() and friends prevents optimization on the server we use, because they're not treated as constants.