日期/时间不同时区之间的转换 [英] date/time Conversion between different timezones

问题描述

我正在使用以下sql将日期/时间值从一个时区转换为另一个时区

I am using the following sql to covert a date/time value from one timezone to another

from_tz(cast(to_date(to_char(q.created_date, 'DDMMYYYY:HH24:MI:SS'),
    'DDMMYYYY:HH24:MI:SS') as timestamp), 'Europe/London') at time zone 'America/New_York'
    else null end as Message_Rcd_Date_Time

从上面的输出如下:

29-OCT-2016 14:28:16.000000 -04:00

29-OCT-2016 14:28:16.000000 -04:00

我想做的是输出如下所示的日期/时间(不包括时区)，请告诉我要实现此目的需要更改什么?

What I want to do is output this date/time as shown below, excluding the timezone, can you please tell me what I need to change in order to achieve this?

29-OCT-2016 14:28:16

29-OCT-2016 14:28:16

推荐答案

首先让我们解决您的表达式

First let's dissolve your expression

FROM_TZ(CAST(TO_DATE(TO_CHAR(q.created_date, 'DDMMYYYY:HH24:MI:SS'), 'DDMMYYYY:HH24:MI:SS') AS TIMESTAMP), 'Europe/London') AT TIME ZONE 'America/New_York'

执行以下操作:

1. TO_CHAR(q.created_date, 'DDMMYYYY:HH24:MI:SS')->将created_date值转换为VARCHAR2
2. TO_DATE(..., 'DDMMYYYY:HH24:MI:SS')->将其转换回DATE
3. CAST(... AS TIMESTAMP)->将其转换为TIMESTAMP(无时区)
4. FROM_TZ(..., 'Europe/London')->在其上附加时区欧洲/伦敦"
5. ... AT TIME ZONE 'America/New_York'->转换为时区"America/New_York"

1. TO_CHAR(q.created_date, 'DDMMYYYY:HH24:MI:SS') -> Convert created_date value to VARCHAR2
2. TO_DATE(..., 'DDMMYYYY:HH24:MI:SS') -> Convert it back to a DATE
3. CAST(... AS TIMESTAMP) -> Convert it to a TIMESTAMP (without time zone)
4. FROM_TZ(..., 'Europe/London') -> Attach time zone 'Europe/London' to it
5. ... AT TIME ZONE 'America/New_York' -> Convert to time zone 'America/New_York'

第1,2和3点毫无用处！由于created_date是TIMESTAMP，因此您可以将其做的更短

Point 1,2 and 3 are useless! Since created_date is a TIMESTAMP you can do it shorter

TO_CHAR(FROM_TZ(q.created_date, 'Europe/London') AT TIME ZONE 'America/New_York', 'DD-MON-YYYY HH24:MI:SS')

如果您的SESSIONTIMEZONE是Europe/London，您甚至可以制作

In case your SESSIONTIMEZONE is Europe/London you can even make

TO_CHAR(q.created_date AT TIME ZONE 'America/New_York', 'DD-MON-YYYY HH24:MI:SS')

这篇关于日期/时间不同时区之间的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！