英文:
Convert UTC to CET (+1 hour) or CEST (+2 hours)
问题
可以将Oracle的UTC时间转换为CET或CEST时间戳吗，即
- 从去年十月的最后一个星期日添加1小时
- 从去年三月的最后一个星期日添加2小时
查询示例
SELECT
NAME,
TO_CHAR(DATE_, 'YYYY-MM-DD HH24:MI:SS') DATE_,
?<MAGIC>? CONVERTED
FROM table
数据
NAME, DATE_, CONVERTED
A, 2022-03-26 18:28:11, 2022-03-26 19:28:11
A, 2022-03-27 17:28:11, 2022-03-27 19:28:11
A, 2022-10-29 17:28:11, 2022-10-29 19:28:11
A, 2022-10-30 18:28:11, 2022-10-30 19:28:11
A, 2023-03-25 18:28:11, 2023-03-25 19:28:11
A, 2023-03-26 17:28:11, 2023-03-26 19:28:11
注意：上述的?<MAGIC>?部分需要根据实际的代码逻辑进行填充。
英文:
Is it possible to convert Oracle UTC to CET or CEST TIMESTAMP, i.e.
- add 1 hour from last October Sunday
- add 2 hours from last March Sunday
Query sample
SELECT
NAME,
TO_CHAR(DATE_, 'YYYY-MM-DD HH24:MI:SS') DATE_,
?<MAGIC>? CONVERTED
FROM table
Data
NAME, DATE_, CONVERTED
A, 2022-03-26 18:28:11, 2022-03-26 19:28:11
A, 2022-03-27 17:28:11, 2022-03-27 19:28:11
A, 2022-10-29 17:28:11, 2022-10-29 19:28:11
A, 2022-10-30 18:28:11, 2022-10-30 19:28:11
A, 2023-03-25 18:28:11, 2023-03-25 19:28:11
A, 2023-03-26 17:28:11, 2023-03-26 19:28:11
答案1
得分: 2
以下是翻译好的部分：
"DATE"数据类型不支持任何时区设置。您可以将其转换为"TIMESTAMP WITH TIME ZONE"，然后修改时区：
SELECT TO_CHAR(
FROM_TZ(CAST(DATE_ AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Zurich',
'YYYY-MM-DD HH24:MI:SS') AS CONVERTED
FROM dual;
请注意，"CET"和"CEST"是模糊的，您应该根据IANA使用完整的时区名称：
SELECT TZNAME, TZABBREV, TZ_OFFSET(TZNAME)
FROM V$TIMEZONE_NAMES
WHERE TZABBREV IN ('CET', 'CEST')
ORDER BY 3, TZNAME;
希望这对您有帮助。
英文:
The DATE datatype does not support any time zone settings. You can convert it to TIMESTAMP WITH TIME ZONE and then modify the time zone:
SELECT TO_CHAR(
FROM_TZ(CAST(DATE_ AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Zurich',
'YYYY-MM-DD HH24:MI:SS') AS CONVERTED
FROM dual;
Note, CET and CEST are ambiguous, you should use full time zone name according IANA:
SELECT TZNAME, TZABBREV, TZ_OFFSET(TZNAME)
FROM V$TIMEZONE_NAMES
WHERE TZABBREV IN ('CET', 'CEST')
ORDER BY 3, TZNAME;
+----------------------------------------------+
|TZNAME |TZABBREV|TZ_OFFSET(TZNAME)|
+----------------------------------------------+
|Africa/Algiers |CET |+01:00 |
|Africa/Algiers |CEST |+01:00 |
|Africa/Casablanca |CET |+01:00 |
|Africa/Tunis |CET |+01:00 |
|Africa/Tunis |CEST |+01:00 |
|Europe/Lisbon |CET |+01:00 |
|Europe/Lisbon |CEST |+01:00 |
|Portugal |CET |+01:00 |
|Portugal |CEST |+01:00 |
|Africa/Ceuta |CET |+02:00 |
|Africa/Ceuta |CEST |+02:00 |
|Africa/Tripoli |CET |+02:00 |
|Africa/Tripoli |CEST |+02:00 |
|Arctic/Longyearbyen|CET |+02:00 |
|Arctic/Longyearbyen|CEST |+02:00 |
|Atlantic/Jan_Mayen |CET |+02:00 |
|Atlantic/Jan_Mayen |CEST |+02:00 |
|CET |CEST |+02:00 |
|CET |CET |+02:00 |
|Europe/Amsterdam |CEST |+02:00 |
|Europe/Amsterdam |CET |+02:00 |
|Europe/Andorra |CET |+02:00 |
|Europe/Andorra |CEST |+02:00 |
|Europe/Belgrade |CET |+02:00 |
|Europe/Belgrade |CEST |+02:00 |
|Europe/Berlin |CET |+02:00 |
|Europe/Berlin |CEST |+02:00 |
|Europe/Bratislava |CET |+02:00 |
|Europe/Bratislava |CEST |+02:00 |
|Europe/Brussels |CET |+02:00 |
|Europe/Brussels |CEST |+02:00 |
|Europe/Budapest |CET |+02:00 |
|Europe/Budapest |CEST |+02:00 |
|Europe/Copenhagen |CET |+02:00 |
|Europe/Copenhagen |CEST |+02:00 |
|Europe/Gibraltar |CET |+02:00 |
|Europe/Gibraltar |CEST |+02:00 |
|Europe/Ljubljana |CET |+02:00 |
|Europe/Ljubljana |CEST |+02:00 |
|Europe/Luxembourg |CET |+02:00 |
|Europe/Luxembourg |CEST |+02:00 |
|Europe/Madrid |CET |+02:00 |
|Europe/Madrid |CEST |+02:00 |
|Europe/Malta |CET |+02:00 |
|Europe/Malta |CEST |+02:00 |
|Europe/Monaco |CET |+02:00 |
|Europe/Monaco |CEST |+02:00 |
|Europe/Oslo |CET |+02:00 |
|Europe/Oslo |CEST |+02:00 |
|Europe/Paris |CEST |+02:00 |
|Europe/Paris |CET |+02:00 |
|Europe/Podgorica |CET |+02:00 |
|Europe/Podgorica |CEST |+02:00 |
|Europe/Prague |CET |+02:00 |
|Europe/Prague |CEST |+02:00 |
|Europe/Rome |CET |+02:00 |
|Europe/Rome |CEST |+02:00 |
|Europe/San_Marino |CET |+02:00 |
|Europe/San_Marino |CEST |+02:00 |
|Europe/Sarajevo |CET |+02:00 |
|Europe/Sarajevo |CEST |+02:00 |
|Europe/Skopje |CET |+02:00 |
|Europe/Skopje |CEST |+02:00 |
|Europe/Stockholm |CET |+02:00 |
|Europe/Stockholm |CEST |+02:00 |
|Europe/Tirane |CET |+02:00 |
|Europe/Tirane |CEST |+02:00 |
|Europe/Vaduz |CET |+02:00 |
|Europe/Vaduz |CEST |+02:00 |
|Europe/Vatican |CET |+02:00 |
|Europe/Vatican |CEST |+02:00 |
|Europe/Vienna |CET |+02:00 |
|Europe/Vienna |CEST |+02:00 |
|Europe/Warsaw |CET |+02:00 |
|Europe/Warsaw |CEST |+02:00 |
|Europe/Zagreb |CET |+02:00 |
|Europe/Zagreb |CEST |+02:00 |
|Europe/Zurich |CET |+02:00 |
|Europe/Zurich |CEST |+02:00 |
|Libya |CET |+02:00 |
|Libya |CEST |+02:00 |
|Poland |CET |+02:00 |
|Poland |CEST |+02:00 |
|Europe/Athens |CEST |+03:00 |
|Europe/Athens |CET |+03:00 |
|Europe/Chisinau |CEST |+03:00 |
|Europe/Chisinau |CET |+03:00 |
|Europe/Kaliningrad |CET |+03:00 |
|Europe/Kaliningrad |CEST |+03:00 |
|Europe/Kiev |CEST |+03:00 |
|Europe/Kiev |CET |+03:00 |
|Europe/Minsk |CEST |+03:00 |
|Europe/Minsk |CET |+03:00 |
|Europe/Riga |CEST |+03:00 |
|Europe/Riga |CET |+03:00 |
|Europe/Simferopol |CEST |+03:00 |
|Europe/Simferopol |CET |+03:00 |
|Europe/Sofia |CET |+03:00 |
|Europe/Sofia |CEST |+03:00 |
|Europe/Tallinn |CET |+03:00 |
|Europe/Tallinn |CEST |+03:00 |
|Europe/Tiraspol |CEST |+03:00 |
|Europe/Tiraspol |CET |+03:00 |
|Europe/Uzhgorod |CET |+03:00 |
|Europe/Uzhgorod |CEST |+03:00 |
|Europe/Vilnius |CET |+03:00 |
|Europe/Vilnius |CEST |+03:00 |
|Europe/Zaporozhye |CEST |+03:00 |
|Europe/Zaporozhye |CET |+03:00 |
+----------------------------------------------+
答案2
得分: 0
SELECT
NAME,
TO_CHAR(DATE_, 'YYYY-MM-DD HH24:MI:SS') AS DATE_,
CASE
WHEN TO_CHAR(DATE_, 'MM') >= '10' AND TO_CHAR(DATE_, 'D') = '1'
THEN TO_CHAR(DATE_ + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS')
WHEN TO_CHAR(DATE_, 'MM') >= '3' AND TO_CHAR(DATE_, 'D') = '1'
THEN TO_CHAR(DATE_ + INTERVAL '2' HOUR, 'YYYY-MM-DD HH24:MI:SS')
ELSE TO_CHAR(DATE_, 'YYYY-MM-DD HH24:MI:SS')
END AS CONVERTED
FROM table;
英文:
SELECT
NAME,
TO_CHAR(DATE_, 'YYYY-MM-DD HH24:MI:SS') AS DATE_,
CASE
WHEN TO_CHAR(DATE_, 'MM') >= '10' AND TO_CHAR(DATE_, 'D') = '1'
THEN TO_CHAR(DATE_ + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS')
WHEN TO_CHAR(DATE_, 'MM') >= '3' AND TO_CHAR(DATE_, 'D') = '1'
THEN TO_CHAR(DATE_ + INTERVAL '2' HOUR, 'YYYY-MM-DD HH24:MI:SS')
ELSE TO_CHAR(DATE_, 'YYYY-MM-DD HH24:MI:SS')
END AS CONVERTED
FROM table;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论