Oraclesql Convert Date Fields Into Another Timezone

in my Oracle DB I have a DATE column where store date values. All date values are in TimeZone Europe/Berlin. Now the application changes its TimeZone to UTC, this means I need to c

Solution 1:

Use FROM_TZ( timestampvalue, timezone ) to convert a timestamp to a timestamp at a specific time zone and then you can use AT TIME ZONE 'UTC' to convert it to the UTC time zone and cast it back to a date:

SELECT CAST(
         FROM_TZ(
           CAST( your_column AS TIMESTAMP ),
           'Europe/Berlin'
         )
         AT TIME ZONE 'UTC'
         AS DATE
       )
FROM   your_table;