Question 227509 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Display timestamptz in a user's timezone

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)?
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search