将UTC转换为CET（+1小时）或CEST（+2小时）

huangapple
go评论147阅读模式
英文:

Convert UTC to CET (+1 hour) or CEST (+2 hours)

问题

可以将Oracle的UTC时间转换为CET或CEST时间戳吗，即

  • 从去年十月的最后一个星期日添加1小时
  • 从去年三月的最后一个星期日添加2小时

查询示例

  1. SELECT
  2.     NAME,
  3.     TO_CHAR(DATE_, 'YYYY-MM-DD HH24:MI:SS') DATE_,
  4.     ?<MAGIC>? CONVERTED
  5. FROM table

数据

  1. NAME, DATE_, CONVERTED
  2. A, 2022-03-26 18:28:11, 2022-03-26 19:28:11
  3. A, 2022-03-27 17:28:11, 2022-03-27 19:28:11
  4. A, 2022-10-29 17:28:11, 2022-10-29 19:28:11
  5. A, 2022-10-30 18:28:11, 2022-10-30 19:28:11
  6. A, 2023-03-25 18:28:11, 2023-03-25 19:28:11
  7. 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

  1. SELECT
  2.     NAME,
  3.     TO_CHAR(DATE_, &#39;YYYY-MM-DD HH24:MI:SS&#39;) DATE_,
  4.     ?&lt;MAGIC&gt;? CONVERTED
  5. FROM table

Data

  1. NAME, DATE_, CONVERTED
  2. A, 2022-03-26 18:28:11, 2022-03-26 19:28:11
  3. A, 2022-03-27 17:28:11, 2022-03-27 19:28:11
  4. A, 2022-10-29 17:28:11, 2022-10-29 19:28:11
  5. A, 2022-10-30 18:28:11, 2022-10-30 19:28:11
  6. A, 2023-03-25 18:28:11, 2023-03-25 19:28:11
  7. A, 2023-03-26 17:28:11, 2023-03-26 19:28:11

答案1

得分: 2

以下是翻译好的部分：

"DATE"数据类型不支持任何时区设置。您可以将其转换为"TIMESTAMP WITH TIME ZONE"，然后修改时区：

  1. SELECT TO_CHAR(
  2.     FROM_TZ(CAST(DATE_ AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Zurich',
  3.     'YYYY-MM-DD HH24:MI:SS') AS CONVERTED
  4. FROM dual;

请注意，"CET"和"CEST"是模糊的，您应该根据IANA使用完整的时区名称：

  1. SELECT TZNAME, TZABBREV, TZ_OFFSET(TZNAME)
  2. FROM V$TIMEZONE_NAMES
  3. WHERE TZABBREV IN ('CET', 'CEST')
  4. 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:

  1. SELECT TO_CHAR(
  2. 	FROM_TZ(CAST(DATE_ AS TIMESTAMP), &#39;UTC&#39;) AT TIME ZONE &#39;Europe/Zurich&#39;,
  3. 	&#39;YYYY-MM-DD HH24:MI:SS&#39;) AS CONVERTED
  4. FROM dual;

Note, CET and CEST are ambiguous, you should use full time zone name according IANA:

  1. SELECT TZNAME, TZABBREV, TZ_OFFSET(TZNAME)
  2. FROM V$TIMEZONE_NAMES
  3. WHERE TZABBREV IN (&#39;CET&#39;, &#39;CEST&#39;)
  4. ORDER BY 3, TZNAME;
  6. +----------------------------------------------+
  7. |TZNAME             |TZABBREV|TZ_OFFSET(TZNAME)|
  8. +----------------------------------------------+
  9. |Africa/Algiers     |CET     |+01:00           |
  10. |Africa/Algiers     |CEST    |+01:00           |
  11. |Africa/Casablanca  |CET     |+01:00           |
  12. |Africa/Tunis       |CET     |+01:00           |
  13. |Africa/Tunis       |CEST    |+01:00           |
  14. |Europe/Lisbon      |CET     |+01:00           |
  15. |Europe/Lisbon      |CEST    |+01:00           |
  16. |Portugal           |CET     |+01:00           |
  17. |Portugal           |CEST    |+01:00           |
  18. |Africa/Ceuta       |CET     |+02:00           |
  19. |Africa/Ceuta       |CEST    |+02:00           |
  20. |Africa/Tripoli     |CET     |+02:00           |
  21. |Africa/Tripoli     |CEST    |+02:00           |
  22. |Arctic/Longyearbyen|CET     |+02:00           |
  23. |Arctic/Longyearbyen|CEST    |+02:00           |
  24. |Atlantic/Jan_Mayen |CET     |+02:00           |
  25. |Atlantic/Jan_Mayen |CEST    |+02:00           |
  26. |CET                |CEST    |+02:00           |
  27. |CET                |CET     |+02:00           |
  28. |Europe/Amsterdam   |CEST    |+02:00           |
  29. |Europe/Amsterdam   |CET     |+02:00           |
  30. |Europe/Andorra     |CET     |+02:00           |
  31. |Europe/Andorra     |CEST    |+02:00           |
  32. |Europe/Belgrade    |CET     |+02:00           |
  33. |Europe/Belgrade    |CEST    |+02:00           |
  34. |Europe/Berlin      |CET     |+02:00           |
  35. |Europe/Berlin      |CEST    |+02:00           |
  36. |Europe/Bratislava  |CET     |+02:00           |
  37. |Europe/Bratislava  |CEST    |+02:00           |
  38. |Europe/Brussels    |CET     |+02:00           |
  39. |Europe/Brussels    |CEST    |+02:00           |
  40. |Europe/Budapest    |CET     |+02:00           |
  41. |Europe/Budapest    |CEST    |+02:00           |
  42. |Europe/Copenhagen  |CET     |+02:00           |
  43. |Europe/Copenhagen  |CEST    |+02:00           |
  44. |Europe/Gibraltar   |CET     |+02:00           |
  45. |Europe/Gibraltar   |CEST    |+02:00           |
  46. |Europe/Ljubljana   |CET     |+02:00           |
  47. |Europe/Ljubljana   |CEST    |+02:00           |
  48. |Europe/Luxembourg  |CET     |+02:00           |
  49. |Europe/Luxembourg  |CEST    |+02:00           |
  50. |Europe/Madrid      |CET     |+02:00           |
  51. |Europe/Madrid      |CEST    |+02:00           |
  52. |Europe/Malta       |CET     |+02:00           |
  53. |Europe/Malta       |CEST    |+02:00           |
  54. |Europe/Monaco      |CET     |+02:00           |
  55. |Europe/Monaco      |CEST    |+02:00           |
  56. |Europe/Oslo        |CET     |+02:00           |
  57. |Europe/Oslo        |CEST    |+02:00           |
  58. |Europe/Paris       |CEST    |+02:00           |
  59. |Europe/Paris       |CET     |+02:00           |
  60. |Europe/Podgorica   |CET     |+02:00           |
  61. |Europe/Podgorica   |CEST    |+02:00           |
  62. |Europe/Prague      |CET     |+02:00           |
  63. |Europe/Prague      |CEST    |+02:00           |
  64. |Europe/Rome        |CET     |+02:00           |
  65. |Europe/Rome        |CEST    |+02:00           |
  66. |Europe/San_Marino  |CET     |+02:00           |
  67. |Europe/San_Marino  |CEST    |+02:00           |
  68. |Europe/Sarajevo    |CET     |+02:00           |
  69. |Europe/Sarajevo    |CEST    |+02:00           |
  70. |Europe/Skopje      |CET     |+02:00           |
  71. |Europe/Skopje      |CEST    |+02:00           |
  72. |Europe/Stockholm   |CET     |+02:00           |
  73. |Europe/Stockholm   |CEST    |+02:00           |
  74. |Europe/Tirane      |CET     |+02:00           |
  75. |Europe/Tirane      |CEST    |+02:00           |
  76. |Europe/Vaduz       |CET     |+02:00           |
  77. |Europe/Vaduz       |CEST    |+02:00           |
  78. |Europe/Vatican     |CET     |+02:00           |
  79. |Europe/Vatican     |CEST    |+02:00           |
  80. |Europe/Vienna      |CET     |+02:00           |
  81. |Europe/Vienna      |CEST    |+02:00           |
  82. |Europe/Warsaw      |CET     |+02:00           |
  83. |Europe/Warsaw      |CEST    |+02:00           |
  84. |Europe/Zagreb      |CET     |+02:00           |
  85. |Europe/Zagreb      |CEST    |+02:00           |
  86. |Europe/Zurich      |CET     |+02:00           |
  87. |Europe/Zurich      |CEST    |+02:00           |
  88. |Libya              |CET     |+02:00           |
  89. |Libya              |CEST    |+02:00           |
  90. |Poland             |CET     |+02:00           |
  91. |Poland             |CEST    |+02:00           |
  92. |Europe/Athens      |CEST    |+03:00           |
  93. |Europe/Athens      |CET     |+03:00           |
  94. |Europe/Chisinau    |CEST    |+03:00           |
  95. |Europe/Chisinau    |CET     |+03:00           |
  96. |Europe/Kaliningrad |CET     |+03:00           |
  97. |Europe/Kaliningrad |CEST    |+03:00           |
  98. |Europe/Kiev        |CEST    |+03:00           |
  99. |Europe/Kiev        |CET     |+03:00           |
  100. |Europe/Minsk       |CEST    |+03:00           |
  101. |Europe/Minsk       |CET     |+03:00           |
  102. |Europe/Riga        |CEST    |+03:00           |
  103. |Europe/Riga        |CET     |+03:00           |
  104. |Europe/Simferopol  |CEST    |+03:00           |
  105. |Europe/Simferopol  |CET     |+03:00           |
  106. |Europe/Sofia       |CET     |+03:00           |
  107. |Europe/Sofia       |CEST    |+03:00           |
  108. |Europe/Tallinn     |CET     |+03:00           |
  109. |Europe/Tallinn     |CEST    |+03:00           |
  110. |Europe/Tiraspol    |CEST    |+03:00           |
  111. |Europe/Tiraspol    |CET     |+03:00           |
  112. |Europe/Uzhgorod    |CET     |+03:00           |
  113. |Europe/Uzhgorod    |CEST    |+03:00           |
  114. |Europe/Vilnius     |CET     |+03:00           |
  115. |Europe/Vilnius     |CEST    |+03:00           |
  116. |Europe/Zaporozhye  |CEST    |+03:00           |
  117. |Europe/Zaporozhye  |CET     |+03:00           |
  118. +----------------------------------------------+

答案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;

英文: 
  1.   SELECT
  2.   NAME,
  3.   TO_CHAR(DATE_, &#39;YYYY-MM-DD HH24:MI:SS&#39;) AS DATE_,
  4.   CASE
  5.     WHEN TO_CHAR(DATE_, &#39;MM&#39;) &gt;= &#39;10&#39; AND TO_CHAR(DATE_, &#39;D&#39;) = &#39;1&#39;
  6.       THEN TO_CHAR(DATE_ + INTERVAL &#39;1&#39; HOUR, &#39;YYYY-MM-DD HH24:MI:SS&#39;)
  7.     WHEN TO_CHAR(DATE_, &#39;MM&#39;) &gt;= &#39;3&#39; AND TO_CHAR(DATE_, &#39;D&#39;) = &#39;1&#39;
  8.       THEN TO_CHAR(DATE_ + INTERVAL &#39;2&#39; HOUR, &#39;YYYY-MM-DD HH24:MI:SS&#39;)
  9.     ELSE TO_CHAR(DATE_, &#39;YYYY-MM-DD HH24:MI:SS&#39;)
  10.   END AS CONVERTED
  11. FROM table;

huangapple
  • 本文由 发表于 2023年6月26日 23:47:12
  • 转载请务必保留本文链接：https://go.coder-hub.com/76558269.html
  • oracle
  • sql
  • time
  • timezone-offset
  • utc
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定