The following query will return the wrong time zone offset. It uses the session time zone instead of the one defined on the time with time zone type:

select formatdatetime(cast('12:00:00+05:15' as time(6) with time zone),'XXX')