This question appeared on IRC, and while I wasn't there while it happened, it caught my eye:
» Can I not render this with timezone offset: select ‘2026-01-09 04:35:46.9824-08'::timestamp with time zone at time zone ‘UTC';
» Returns ‘2026-01-09 12:35:46.9824' which is without the offset.
Let's see what can be done about it.
First, let's see current state of my test PostgreSQL:
=$ show timezone; TimeZone --------------- Europe/Warsaw (1 row) =$ select now(); now ------------------------------- 2026-01-27 09:14:13.630333+01 (1 row) =$ select '2026-01-09 04:35:46.9824-08'::timestamp with time zone; timestamptz ----------------------------- 2026-01-09 13:35:46.9824+01 (1 row) =$ select '2026-01-09 04:35:46.9824-08'::timestamp with time zone at time zone 'UTC'; timezone -------------------------- 2026-01-09 12:35:46.9824 (1 row)
It looks that my local pg is in UTC+01 time zone, which can be seen in now() output. When given timestamptz with -08 timezone information, it got correctly converted to appropriate timestamp in my local timezone (9 hours later).
But when doing the at time zone ‘UTC', output didn't have +00. Why? Well, the reason is actually pretty simple. All timestamptzs are presented to user, in Pg, always, with the user local timezone. So, to see the value with +00, I'd need to:
=$ set timezone = UTC; SET =$ select '2026-01-09 04:35:46.9824-08'::timestamp with time zone; timestamptz ----------------------------- 2026-01-09 12:35:46.9824+00 (1 row)
Amazing. Simple. Solved. But is it? The problem with this approach is that timezone will stay at UTC. Of course I could:
=$ reset timezone;
RESETBut perhaps there is better approach? Actually, there is. We can use custom function that will wrap the timezone change, and return the value. The small issue with it is that the function can't return timestamptz – because then it would get immediately converted to user's timezone on display. But the function can return text, and it should work well:
=$ create function tstz_at_timezone(IN p_ts timestamptz, IN p_timezone text) returns text as $$ declare v_ret TEXT; BEGIN execute format('set timezone = %L', p_timezone); v_ret := p_ts::text; reset timezone; RETURN v_ret; END; $$ language plpgsql; CREATE FUNCTION =$ select now(); now ------------------------------- 2026-01-27 09:26:36.431995+01 (1 row) =$ select tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'UTC' ); tstz_at_timezone ----------------------------- 2026-01-09 12:35:46.9824+00 (1 row) =$ select now(); now ------------------------------- 2026-01-27 09:26:36.432725+01 (1 row)
I added the select now() before and after to show that timezone for client hasn't been changed.
The critical part of the whole process is setting timezone in the function, to whatever user wants, and resetting it back to whatever it was before after function is done with making text representation.
As you can see I can now show the offset for any timezone, for example:
=$ select tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'Asia/Kathmandu' ); tstz_at_timezone -------------------------------- 2026-01-09 18:20:46.9824+05:45 (1 row) =$ select tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'Pacific/Galapagos' ); tstz_at_timezone ----------------------------- 2026-01-09 06:35:46.9824-06 (1 row) =$ select tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'UTC+5' ); tstz_at_timezone ----------------------------- 2026-01-09 07:35:46.9824-05 (1 row)
Is there anything else? Well, one thing comes to mind. to_char function.
If I'd want the ts to be formatted with to_char, I still can't really do it easily:
=$ select to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF'); to_char ------------------------ 2026-01-27T09:35:00+01 (1 row) =$ select to_char(tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'Pacific/Galapagos' ), 'YYYY-MM-DD"T"HH24:MI:SSOF'); ERROR: function to_char(text, unknown) does not exist LINE 1: select to_char(tstz_at_timezone( '2026-01-09 04:35:46.9824-0... ^ DETAIL: No function of that name accepts the given argument types. HINT: You might need to add explicit type casts. =$ select to_char(tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'Pacific/Galapagos' )::timestamptz, 'YYYY-MM-DD"T"HH24:MI:SSOF'); to_char ------------------------ 2026-01-09T13:35:46+01 (1 row)
As you can see, I can use OF format for to_char to display timezone offset. But if I pass it output from tstz_at_timezone() – it fails, because it expects timestamptz, and not text, and if I cast to timestamptz – then the timezone information is lost, because – well, timestamps are always in client timezone.
Is there a way around it? Sure. We can make our function to take optional argument, and if it's there, let's use to_char:
=$ drop function tstz_at_timezone(timestamptz, text); DROP FUNCTION =$ create function tstz_at_timezone(IN p_ts timestamptz, IN p_timezone text, IN p_format TEXT = NULL ) returns text as $$ declare v_ret TEXT; BEGIN execute format('set local timezone = %L', p_timezone); IF p_format IS NULL THEN v_ret := p_ts::text; ELSE v_ret := to_char( p_ts, p_format ); END IF; RESET timezone; RETURN v_ret; END; $$ language plpgsql; CREATE FUNCTION
With this in place, I can still run previous examples just fine:
=$ select tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'UTC' ); tstz_at_timezone ----------------------------- 2026-01-09 12:35:46.9824+00 (1 row) =$ select tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'Europe/Warsaw' ); tstz_at_timezone ----------------------------- 2026-01-09 13:35:46.9824+01 (1 row) =$ select tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'UTC+2' ); tstz_at_timezone ----------------------------- 2026-01-09 10:35:46.9824-02 (1 row)
But now I can also provide to_char formats:
=$ select tstz_at_timezone( '2026-01-09 04:35:46.9824-08', 'Asia/Kathmandu', 'YYYY-MM-DD"T"HH24:MI:SSOF' ); tstz_at_timezone --------------------------- 2026-01-09T18:20:46+05:45 (1 row)
Sweet. Hope it will help someone, was fun to write 🙂