oracle 将UTC转换为CET(+1小时)或CEST(+2小时)

y53ybaqx 于 2023-06-29 发布在 Oracle

关注(0)|答案(2)|浏览(293)

是否可以将Oracle UTC转换为CET或CEST TIMESTAMP，即

从去年10月的星期日增加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

oracle

来源：https://stackoverflow.com/questions/76558269/convert-utc-to-cet-1-hour-or-cest-2-hours

2条答案

按热度按时间

pes8fvy91#

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;

+----------------------------------------------+
|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           |
+----------------------------------------------+

赞(0）回复(0）举报 2023-06-29

eyh26e7m2#

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;

赞(0）回复(0）举报 2023-06-29

我来回答

oracle 将UTC转换为CET(+1小时)或CEST(+2小时)

2条答案

相关问题

热门标签

最新问答