英文:
How to add a months to a timestamp with time zone value while keeping the exact same hour
问题
我有一个PL/SQL变量，类型是TIMESTAMP WITH TIME ZONE，我想要在其值上添加正好一个月的时间。但是尤其是在从夏时制到标准时制的转换时，时间应保持不变。
以下的SELECT语句（虽然不是PL/SQL）展示了这个问题：
WITH
data
AS
( SELECT TO_TIMESTAMP_TZ('2023-04-17 05:00:00 EUROPE/BERLIN', 'yyyy-mm-dd hh24:mi:ss tzr') + NUMTOYMINTERVAL(LEVEL, 'MONTH') AS ts
FROM DUAL
CONNECT BY LEVEL <= 7)
SELECT to_char(ts, 'yyyy-mm-dd hh24:mi:ss tzr') AS ts
FROM data;
这会返回以下结果：
2023-05-17 05:00:00 Europe/Berlin
2023-06-17 05:00:00 Europe/Berlin
2023-07-17 05:00:00 Europe/Berlin
2023-08-17 05:00:00 Europe/Berlin
2023-09-17 05:00:00 Europe/Berlin
2023-10-17 05:00:00 Europe/Berlin
2023-11-17 04:00:00 Europe/Berlin
正如您所看到的，在11月份时，时间变为了4点。
在Oracle中，是否有一种函数可以添加一个月而不改变时间？
英文:
I have a PL/SQL variable of type TIMESTAMP WITH TIME ZONE to whose value I want to add exactly one month.
But especially the time should remain identical when changing from daylight saving time to standard time.
The following SELECT (although not PLS/SQL) shows the problem:
WITH
data
AS
( SELECT TO_TIMESTAMP_TZ('2023-04-17 05:00:00 EUROPE/BERLIN', 'yyyy-mm-dd hh24:mi:ss tzr') + NUMTOYMINTERVAL(LEVEL, 'MONTH') AS ts
FROM DUAL
CONNECT BY LEVEL <= 7)
SELECT to_char(ts, 'yyyy-mm-dd hh24:mi:ss tzr') AS ts
FROM data;
This returns
2023-05-17 05:00:00 Europe/Berlin
2023-06-17 05:00:00 Europe/Berlin
2023-07-17 05:00:00 Europe/Berlin
2023-08-17 05:00:00 Europe/Berlin
2023-09-17 05:00:00 Europe/Berlin
2023-10-17 05:00:00 Europe/Berlin
2023-11-17 04:00:00 Europe/Berlin
As you can see the time changes to 4 o'clock in November.
Is there a function in Oracle to add a month without changing the time?
答案1
得分: 2
你可以尝试这个：
FROM_TZ(
CAST(
TO_TIMESTAMP_TZ('2023-04-17 05:00:00 EUROPE/BERLIN', 'yyyy-mm-dd hh24:mi:ss tzr') AS TIMESTAMP
) + NUMTOYMINTERVAL(LEVEL, 'MONTH'),
'Europe/Berlin'
)
CAST(... AS TIMESTAMP) 移除了时区（和夏令时）信息，然后添加了月份，之后你可以再次使用 FROM_TZ 添加时区信息。
英文:
You can try this:
FROM_TZ(
CAST(
TO_TIMESTAMP_TZ('2023-04-17 05:00:00 EUROPE/BERLIN', 'yyyy-mm-dd hh24:mi:ss tzr') AS TIMESTAMP
) + NUMTOYMINTERVAL(LEVEL, 'MONTH'),
'Europe/Berlin'
)
CAST(... AS TIMESTAMP) removes the time zone (and DST) information, then it adds the months and afterwards you attach time zone again with FROM_TZ
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论