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.
- 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.
- I cannot execute two separate statements separated by
;
in aPreparedStatement
- I do not want to change the default time zone for the database, since there may be multiple concurrent requests.
- 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)?
2
Answers
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:
This has (at least) two problems:
java.time
, the performance is worse, not better.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.
Just use the clause
at time zone
to convert server’s timestamptz to user’s time zone, here an example:It’s necessary to "manually" add the offset to the desired result since the expression
timestamp with time zone AT TIME ZONE zone
returnstimestamp without time zone
as can be seen in official documentation