Question

Im公司正在开发一个P Spring Boot应用程序,查询邮政总局的数据库。

页: 1 我的申请的最终使用者希望自己在时间区看到这些时间点,但所抵消之处包括。例如,

Given 载有<编码>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.

如果我是在科索尔执行,它给我exact<>em>。因此,我希望

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

然而,我不知道如何在我的春天布道申请中利用这一点。

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 a PreparedStatement
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.

因此,回答其中任何一个问题都会大大有助于:

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

仅使用<代码>在时间区,将服务器时间设计改为用户时区,例如:

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;

有必要将“manually”一词添加到所期望的结果中,因为以下文字可看出:timestamp with time area AT TIME ZONE Zone>timestamp without time zones ,可在

Answer 2

引出这一点非常复杂。这里的职能是,通过把所有佩佩斯人的思想从他的回答和评论结合起来,与他们一道满足原来的要求:

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;

这有两个问题:

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

另一种选择是,履行我原来的职责,但执行情况更糟。

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

Answer 3

登出的取决于本届会议的<条码>日,如果UTC确实如此,就会出现不正确的抵消。

而且,它比它所需要的更加复杂。相反:

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$;

这种较简单的等同比在我的测验中几乎快两倍。

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?

罗马尼亚见:

PARALLEL label for a function with SELECT and INSERT

承诺使用的任务比PL/pgSQL需要多,而后者比较昂贵。

或较短,但配有平原:

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和SL的职能略有不同。可在大盘点中加固这一功能,并明显更快。见:

Function Performance

或许,读到<代码>文本的平原足够了吗? 表格略有改动:

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$;

速度加快。

友情链接