Is it possible to convert Oracle UTC to CET or CEST TIMESTAMP, i.e.
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
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 |
+----------------------------------------------+