oracle 在表中创建具有时间数据类型和插入时间数据的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27578072/
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
create table with time datatype and insertion time data in table
提问by som roul
I want to insert a time data in a table . So, what type of datatypes, I should use and how can I insert a time in a table? I used 'timestamp' in create table statement, but when I insert a time in table, it shows an error (error-code : ORA-01861). Please help me. I use ORACLE 11g.
我想在表中插入时间数据。那么，我应该使用什么类型的数据类型以及如何在表中插入时间？我在创建表语句中使用了“时间戳”，但是当我在表中插入时间时，它显示一个错误（错误代码：ORA-01861）。请帮我。我使用 ORACLE 11g。
DDL statement:
DDL 声明：
CREATE TABLE "SOM"."FLIGHTS"
( "FLNO" NUMBER(3,0) NOT NULL ENABLE,
"FROM" VARCHAR2(20 BYTE),
"TO" VARCHAR2(20 BYTE),
"DISTANCE" NUMBER(5,3),
"DEPARTS" TIMESTAMP (3),
"ARRIVES" TIMESTAMP (3),
"PRICE" NUMBER(5,2),
CONSTRAINT "FLIGHTS_PK" PRIMARY KEY ("FLNO") );
insert statement:
插入语句：
insert into flights
values (1, 'Bbsr', 'Calcutta', 600, timestamp '05:30', timestamp '06:50', 8000);
回答by APC
The message for ORA-01861 is literal does not match format string. This error means we're casting a string to another data type but the string has the wrong format.
ORA-01861 的消息是literal does not match format string. 此错误意味着我们将字符串转换为另一种数据类型，但该字符串的格式错误。
Your insert statement features two casts of a string to a TIMESTAMP datatype using the ANSI SQL style timestamp()function. This function takes prescribed formats: YYYY-MM-DD HH24.MI.SS, YYYY-MM-DD HH24.MI.SS.FFand YYYY-MM-DD HH24.MI.SS.FF TZ.
您的插入语句使用 ANSI SQL 样式timestamp()函数将字符串两次转换为 TIMESTAMP 数据类型。此函数采用规定的格式：YYYY-MM-DD HH24.MI.SS,YYYY-MM-DD HH24.MI.SS.FF和YYYY-MM-DD HH24.MI.SS.FF TZ。
You are getting ORA-01861 because your string does not match one of those formats: timestamp '05:30'.
您收到 ORA-01861，因为您的字符串与以下格式之一不匹配：timestamp '05:30'.
In this case the solution is to change the column datatype: you do not want a timestamp, which is a specific time on a specific date. What you want is just the flight's take-off and landing times, which would be better expressed as number(4,2)or varchar2(5)which a check constraint.
在这种情况下，解决方案是更改列数据类型：您不需要时间戳，它是特定日期的特定时间。您想要的只是航班的起飞和着陆时间，这可以更好地表示为number(4,2)或varchar2(5)检查约束。
Two other observations.
另外两个观察。
Your insert statement has numeric value which are too big for the specified number columns, which is easy to fix.
您的插入语句的数值对于指定的数字列来说太大了，这很容易修复。
Your table definition uses oracle reserved words as column names, TO and FROM. The create statement succeeds only because you have wrapped everything in double quotes, so Oracle doesn't validate the names. Using reserved words like this is incredibly bad practice. Every time we refer to the columns we will need to write ...
您的表定义使用 oracle 保留字作为列名，TO 和 FROM。create 语句之所以成功，只是因为您将所有内容都用双引号括起来，因此 Oracle 不会验证名称。像这样使用保留字是非常糟糕的做法。每次我们引用我们需要写的列时......
select flno, "FROM", "TO" from flights;
... otherwise the we'll get ORA-00936 (SQLFiddle demo). The poor saps who have to use your table and maintain your code will curse you forever. Don't be that guy.
...否则我们将得到 ORA-00936（SQLFiddle 演示）。必须使用您的表并维护您的代码的可怜的 sap 将永远诅咒您。不要成为那个人。
