OracleSQL将日期字段转换为另一个时区

问题描述

有一个DATE列，用于存储日期值.所有日期值都在TimeZone Europe/Berlin中.现在，应用程序将其TimeZone更改为UTC，这意味着我需要将所有现有的Dates从Europe/Berlin转换为UTC.

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 convert all existing Dates from Europe/Berlin into UTC.

是否可以在Oracle中本地执行此操作?

Is there a way to do this natively in Oracle?

推荐答案

使用FROM_TZ( timestampvalue, timezone )将时间戳转换为特定时区的时间戳，然后可以使用AT TIME ZONE 'UTC'将其转换为UTC时区，将其投放回日期:

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;

这篇关于OracleSQL将日期字段转换为另一个时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！