Oracle 日期数据类型，通过 SQL 转换为 'YYYY-MM-DD HH24:MI:SS TMZ'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15833030/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Date datatype, transformed to 'YYYY-MM-DD HH24:MI:SS TMZ' through SQL
提问by James Gallagher
I have an application that uploads to an Oracle Data datatype column via:
我有一个通过以下方式上传到 Oracle 数据类型列的应用程序：
TO_TIMESTAMP_TZ('2012-10-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR')
I now need to pull the following format and timezone from this Data column: 'YYYY-MM-DD HH24:MI:SS CDT'
我现在需要从此数据列中提取以下格式和时区：'YYYY-MM-DD HH24:MI:SS CDT'
Note: that the date is uploaded in CST but needs to be returned in CDT.
注意：日期以 CST 上传，但需要以 CDT 返回。
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
I have tried the following also:
我也尝试了以下方法：
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?
我怎么解决这个问题？
回答by Vincent Malgrat
There's a bit of confusion in your question:
你的问题有点混乱：
- a
Datedatatype doesn't save the time zone component. This piece of information is truncated and lost forever when you insert aTIMESTAMP WITH TIME ZONEinto aDate. - 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_CHARfunction. In Oracle, aDatehas no format: it is a point in time. - Reciprocally, you would use
TO_TIMESTAMP_TZto convert aVARCHAR2to aTIMESTAMP, but this won't convert aDateto aTIMESTAMP. - You use
FROM_TZto add the time zone information to aTIMESTAMP(or aDate). - In Oracle,
CSTis a time zone butCDTis not.CDTis a daylight saving information. - To complicate things further,
CST/CDT(-05:00) andCST/CST(-06:00) will have different values obviously, but the time zoneCSTwill inherit the daylight saving information depending upon the date by default.
- 一个
Date数据类型不保存时区的组成部分。当您将 a 插入TIMESTAMP WITH TIME ZONE到Date. - 当您想在屏幕上显示日期或通过字符 API（XML、文件...）将其发送到另一个系统时，您可以使用该
TO_CHAR函数。在 Oracle 中，aDate没有格式：它是一个时间点。 - 相反，您可以使用
TO_TIMESTAMP_TZ将 a 转换VARCHAR2为 aTIMESTAMP，但这不会将 a 转换Date为 aTIMESTAMP。 - 您用于
FROM_TZ将时区信息添加到 aTIMESTAMP（或 aDate）。 - 在 Oracle 中，
CST是一个时区，但CDT不是。CDT是夏令时信息。 - 更复杂的是，
CST/CDT(-05:00) 和CST/CST(-06:00) 显然会有不同的值，但CST默认情况下，时区会根据日期继承夏令时信息。
So your conversion may not be as simple as it looks.
所以你的转换可能不像看起来那么简单。
Assuming that you want to convert a Datedthat you know is valid at time zone CST/CSTto the equivalent at time zone CST/CDT, you would use:
假设您想将Dated您知道在 time zone 有效的a 转换为在 time zoneCST/CST的等效项CST/CDT，您将使用：
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
回答by DazzaL
to convert a TimestampTZ in oracle, you do
要在 oracle 中转换 TimestampTZ，您可以
TO_TIMESTAMP_TZ('2012-10-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR')
at time zone 'region'
see here: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG264
见这里：http: //docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLPG264
and here for regions: http://docs.oracle.com/cd/E11882_01/server.112/e10729/applocaledata.htm#NLSPG0141
这里是区域：http: //docs.oracle.com/cd/E11882_01/server.112/e10729/applocaledata.htm#NLSPG0141
eg:
例如：
SQL> select a, sys_extract_utc(a), a at time zone '-05:00' from (select TO_TIMESTAMP_TZ('2013-04-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR') a from dual);
A
---------------------------------------------------------------------------
SYS_EXTRACT_UTC(A)
---------------------------------------------------------------------------
AATTIMEZONE'-05:00'
---------------------------------------------------------------------------
09-APR-13 01.10.21.000000000 CST
09-APR-13 06.10.21.000000000
09-APR-13 01.10.21.000000000 -05:00
SQL> select a, sys_extract_utc(a), a at time zone '-05:00' from (select TO_TIMESTAMP_TZ('2013-03-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR') a from dual);
A
---------------------------------------------------------------------------
SYS_EXTRACT_UTC(A)
---------------------------------------------------------------------------
AATTIMEZONE'-05:00'
---------------------------------------------------------------------------
09-MAR-13 01.10.21.000000000 CST
09-MAR-13 07.10.21.000000000
09-MAR-13 02.10.21.000000000 -05:00
SQL> select a, sys_extract_utc(a), a at time zone 'America/Los_Angeles' from (select TO_TIMESTAMP_TZ('2013-04-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR') a from dual);
A
---------------------------------------------------------------------------
SYS_EXTRACT_UTC(A)
---------------------------------------------------------------------------
AATTIMEZONE'AMERICA/LOS_ANGELES'
---------------------------------------------------------------------------
09-APR-13 01.10.21.000000000 CST
09-APR-13 06.10.21.000000000
08-APR-13 23.10.21.000000000 AMERICA/LOS_ANGELES
