时区的 Oracle 日期格式掩码是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2291082/
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
What is the Oracle date formatting mask for time zones?
提问by dacracot
I need to insert a date format from an outside source which includes the three letter code for time zone, but the TZDformatting mask does not seem to work...
我需要从外部来源插入一个日期格式，其中包括时区的三个字母代码，但TZD格式掩码似乎不起作用......
insert into blah
values (to_date('Thu, 18 Feb 2010 08:37:00 EST','Dy, DD Mon YYYY HH24:MI:SS TZD'));
ORA-01821: date format not recognized
If I remove the "TZD"...
如果我删除“TZD”...
insert into blah
values (to_date('Thu, 18 Feb 2010 08:37:00','Dy, DD Mon YYYY HH24:MI:SS'));
1 row created.
What is the proper mask for such an insert statement in Oracle?
Oracle 中这种插入语句的正确掩码是什么？
desc blah
Name Null? Type
----------------------------------------- -------- ----------------------------
D DATE
Edit: I changed the table column from DATE type to TIMESTAMP type and got the same error.
编辑：我将表列从 DATE 类型更改为 TIMESTAMP 类型并得到相同的错误。
回答by Adam Musch
Date columns don't have timezone as an option. You'd have to create the column as data type TIMESTAMP WITH TIME ZONEorTIMESTAMP WITH LOCAL TIME ZONE, and besides, the TO_DATEfunction doesn't understand the TIME ZONE format mask you're applying.
日期列没有时区作为选项。您必须将列创建为数据类型TIMESTAMP WITH TIME ZONEor TIMESTAMP WITH LOCAL TIME ZONE，此外，该TO_DATE函数无法理解您正在应用的 TIME ZONE 格式掩码。
SQL> CREATE TABLE T
2 (DT DATE,
3 TS TIMESTAMP,
4 TSTZ TIMESTAMP WITH TIME ZONE,
5 TSLTZ TIMESTAMP WITH LOCAL TIME ZONE);
Table created.
SQL> INSERT INTO T (TSLTZ) VALUES
2 (to_timestamp_tz('Thu, 18 Feb 2010 08:37:00 EST','DY, DD Mon YYYY HH24:MI:SS TZD'))
3 /
1 row created.
SQL> INSERT INTO T (TSTZ) VALUES
2 (to_timestamp_tz('Thu, 18 Feb 2010 08:37:00 EST','DY, DD Mon YYYY HH24:MI:SS TZD'))
3 /
1 row created.
回答by Ananth N
If timezone is not relevant for you, just strip it from the string using SUBSTR function and insert as in your second example.
如果时区与您无关，只需使用 SUBSTR 函数将其从字符串中剥离，然后按照第二个示例进行插入。
