Display timestamptz in a user's timezone

Question

I'm working on a Spring Boot application which queries a PostgreSQL database.

I have a timestamptz column. The end users of my application would like to view these timestamps in their own timezone, with the offset included. For example,

Given a row containing 2023-08-10T12:00:00.000+00

When the user queries from time zone 'US/Eastern'

Then the output they should see is 2023-08-10T08:00:00.000-04

Sounds simple enough, but I'm having trouble figuring out how to handle this given that there will be multiple concurrent requests coming from different time zones.

If I execute this in a SQL console, it gives me the exact result I want,

SET LOCAL TIME ZONE 'US/Eastern';
SELECT to_char('2023-08-10T12:00:00.000+00'::TIMESTAMPTZ, 'YYYY-MM-DD"T"HH24:MI:SS.MSOF');

2023-08-10T08:00:00.000000-04

However, I do not know how to use this from within my Spring Boot application.

1. According to my benchmarks, if I try to do this in code, it comes with a ~30% performance penalty, which is significant in this case, so I would like to do it entirely in SQL if possible.
2. I cannot execute two separate statements separated by ; in a PreparedStatement
3. I do not want to change the default time zone for the database, since there may be multiple concurrent requests.
4. For the same reason, I do not want to change the default time zone for the JVM.

So, an answer to either of these questions would be a huge help:

• In jdbc, is there a way to set the time zone for only a single query, without affecting other concurrent queries?
• In PostgreSQL, is there a way I can express the above SQL in a single line (no semi colons)?

Answer 1

The function you posted depends on the timezone setting of the current session, and shows an incorrect offset if that doesn't happen to be UTC.

Also, it's more convoluted than it needs to be. Try instead:

CREATE OR REPLACE FUNCTION display_at_zone(tstz timestamptz, display_zone text)
  RETURNS text
  LANGUAGE plpgsql STABLE PARALLEL SAFE AS   -- not IMMUTABLE!
$func$
DECLARE
   at_zone timestamp := tstz AT TIME ZONE display_zone;
BEGIN
   RETURN to_char(at_zone, 'YYYY-MM-DD"T"HH24:MI:SS.MS')
       || to_char(at_zone - tstz AT TIME ZONE 'UTC', 'HH:MI');   -- FIX!
END
$func$;

This simpler equivalent is about twice as fast as your function in my tests.

Note how I work with at_zone - tstz AT TIME ZONE 'UTC' instead of at_zone - tstz to get the correct offset, independent of the current timezone setting.

Make it STABLE, not IMMUTABLE, because to_char() is only STABLE. So it can be "inlined" in an outer query. See:

• Can declaring function volatility IMMUTABLE harm performance?

Make it PARALLEL SAFE (because it is) so it does not stand in the way of parallelization. See:

• PARALLEL label for a function with SELECT and INSERT

Don't use more assignments than needed in PL/pgSQL, where those are comparatively expensive.

Or shorter, yet, with plain SQL:

CREATE OR REPLACE FUNCTION display_at_zone_sql(tstz timestamptz, display_zone text)
  RETURNS text
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT to_char(tstz AT TIME ZONE display_zone, 'YYYY-MM-DD"T"HH24:MI:SS.MS')
    || to_char(tstz AT TIME ZONE display_zone
             - tstz AT TIME ZONE 'UTC', 'HH:MI');
$func$;

PL/pgSQL and SQL functions have slightly different performance characteristics. This SQL function can be inlined and is noticeably faster when nested in a bigger query. See:

• Function Performance

Maybe, a plain cast to text is good enough? Differs slightly from your format:

CREATE OR REPLACE FUNCTION display_at_zone_sql3(tstz timestamptz, display_zone text)
  RETURNS text
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT (tstz AT TIME ZONE display_zone)::text       -- does the job?
    || to_char(tstz AT TIME ZONE display_zone
             - tstz AT TIME ZONE 'UTC', 'HH:MI');
$func$;

Faster, yet.

Answer 2

Just use the clause at time zone to convert server's timestamptz to user's time zone, here an example:

select '2023-08-10T12:00:00.000+00' original_timestamptz
,'2023-08-10T12:00:00.000+00' at time zone 'US/Eastern' converted,
split_part(to_char('2023-08-10T12:00:00.000+00' at time zone 'US/Eastern', 
'YYYY-MM-DD"T"HH24:MI:SS.MSOF'),'+',1)||
(select split_part(utc_offset::text,':',1) from pg_timezone_names where name = 'US/Eastern')
with_offset;

It's necessary to "manually" add the offset to the desired result since the expression timestamp with time zone AT TIME ZONE zone returns timestamp without time zone as can be seen in official documentation

Answer 3

Turns out this is quite complicated. Here is a function I've come up with which meets the original requirements by combining all of Pepe's ideas from his answer and comments:

CREATE OR REPLACE FUNCTION display_at_zone(
    tstz         TIMESTAMPTZ,
    display_zone VARCHAR
) RETURNS VARCHAR
AS
$$
DECLARE
    at_zone        TIMESTAMP;
    utc_offset     INTERVAL;
    hours          INT;
    minutes        INT;
    string_no_zone VARCHAR;
BEGIN

    at_zone = tstz AT TIME ZONE display_zone;
    utc_offset = at_zone - tstz;
    hours = EXTRACT(HOURS FROM utc_offset);
    minutes = ABS(EXTRACT(MINUTES FROM utc_offset));
    string_no_zone = TO_CHAR(at_zone, 'YYYY-MM-DD"T"HH24:MI:SS.MS');

    RETURN string_no_zone || TO_CHAR(hours, 'SG00:') || TO_CHAR(minutes, 'FM00');

END
$$ IMMUTABLE LANGUAGE plpgsql;

This has (at least) two problems:

1. Compared to doing the exact same operation using java.time, the performance is worse, not better.
2. Does not work if the display_timezone parameter is an offset, but this can be easily worked around.

Another option is to do my original suggestion in a function, but the performance of this is even worse.

BEGIN
    PERFORM set_config('timezone', display_zone, true /* local */);
    RETURN to_char(tstz, 'YYYY-MM-DD"T"HH24:MI:SS.MSOF');
END