Oracle日期数据类型，已通过SQL转换为"YYYY-MM-DD HH24:MI:SS TMZ" [英] Oracle Date datatype, transformed to 'YYYY-MM-DD HH24:MI:SS TMZ' through SQL

问题描述

我有一个应用程序，它可以通过以下方式上传到Oracle数据类型列:

I have an application that uploads to an Oracle Data datatype column via:

TO_TIMESTAMP_TZ('2012-10-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR')

我现在需要从数据"列中提取以下格式和时区: 'YYYY-MM-DD HH24:MI:SS CDT'

I now need to pull the following format and timezone from this Data column: 'YYYY-MM-DD HH24:MI:SS CDT'

注意:该日期是在CST中上传的，但需要在CDT中返回.

Note: that the date is uploaded in CST but needs to be returned in CDT.

我有Google，但仅在Date数据类型中找到以下内容:

I have Google'd but have only found following for Date datatypes:

SELECT dateColumn From dateTable;
09-NOV-12

SELECT TO_CHAR(dateColumn,'YYYY-MM-DD HH24:MI:SS') From dateTable;
2012-10-09 1:10:21

我也尝试了以下方法:

TO_TIMESTAMP_TZ(dateColumn,'YYYY-MM-DD HH24:MI:SS CDT')
data format not recognized

TO_TIMESTAMP_TZ(CRTE_DT,'YYYY-MM-DD HH24:MI:SS TZH:TZM')
09-NOV-12 1:10:21 AM +01:00
don't understand why this does not come back as YYYY-MM-DD?

我该如何解决这个问题?

How can I solve this problem?

推荐答案

您的问题中有些困惑:

• Date数据类型不保存时区分量.当您将TIMESTAMP WITH TIME ZONE插入Date时，此信息将被永远截断并丢失.
• 当您想在屏幕上显示日期或通过字符API(XML，文件...)将日期发送到另一个系统时，可以使用 TO_TIMESTAMP_TZ 来转换VARCHAR2转换为TIMESTAMP，但这不会将Date转换为TIMESTAMP.
• 您使用FROM_TZ将时区信息添加到TIMESTAMP(或Date).
• 在Oracle中，CST是时区，但CDT不是. CDT是夏令时信息.
• 进一步复杂化的是，CST/CDT(-05:00)和CST/CST(-06:00)显然具有不同的值，但是默认情况下，时区CST会根据日期继承夏时制信息

• a Date datatype doesn't save the time zone component. This piece of information is truncated and lost forever when you insert a TIMESTAMP WITH TIME ZONE into a Date.
• When you want to display a date, either on screen or to send it to another system via a character API (XML, file...), you use the TO_CHAR function. In Oracle, a Date has no format: it is a point in time.
• Reciprocally, you would use TO_TIMESTAMP_TZ to convert a VARCHAR2 to a TIMESTAMP, but this won't convert a Date to a TIMESTAMP.
• You use FROM_TZ to add the time zone information to a TIMESTAMP (or a Date).
• In Oracle, CST is a time zone but CDT is not. CDT is a daylight saving information.
• To complicate things further, CST/CDT (-05:00) and CST/CST (-06:00) will have different values obviously, but the time zone CST will inherit the daylight saving information depending upon the date by default.

所以您的转换可能并不像看起来那样简单.

So your conversion may not be as simple as it looks.

假定您要将已知在时区CST/CST有效的Date d转换为时区CST/CDT的等效内容，则可以使用:

Assuming that you want to convert a Date d that you know is valid at time zone CST/CST to the equivalent at time zone CST/CDT, you would use:

SQL> SELECT from_tz(d, '-06:00') initial_ts,
  2         from_tz(d, '-06:00') at time zone ('-05:00') converted_ts
  3    FROM (SELECT cast(to_date('2012-10-09 01:10:21',
  4                              'yyyy-mm-dd hh24:mi:ss') as timestamp) d
  5            FROM dual);

INITIAL_TS                      CONVERTED_TS
------------------------------- -------------------------------
09/10/12 01:10:21,000000 -06:00 09/10/12 02:10:21,000000 -05:00

这里使用了我的默认时间戳格式.我可以明确指定格式:

My default timestamp format has been used here. I can specify a format explicitely:

SQL> SELECT to_char(from_tz(d, '-06:00'),'yyyy-mm-dd hh24:mi:ss TZR') initial_ts,
  2         to_char(from_tz(d, '-06:00') at time zone ('-05:00'),
  3                 'yyyy-mm-dd hh24:mi:ss TZR') converted_ts
  4    FROM (SELECT cast(to_date('2012-10-09 01:10:21',
  5                              'yyyy-mm-dd hh24:mi:ss') as timestamp) d
  6            FROM dual);

INITIAL_TS                      CONVERTED_TS
------------------------------- -------------------------------
2012-10-09 01:10:21 -06:00      2012-10-09 02:10:21 -05:00

这篇关于Oracle日期数据类型，已通过SQL转换为"YYYY-MM-DD HH24:MI:SS TMZ"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！