Oracle 时间戳数据类型缺少时间信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5477055/
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 timestamp datatype is missing time information
提问by vradenburg
I'm trying to store a datetime value in oracle from .net and when the date time gets persisted to the db, the time information is missing.
我正在尝试从 .net 在 oracle 中存储日期时间值，当日期时间保存到数据库时，时间信息丢失。
The db is 11g and I'm using the OracleCommand and OracleConnection classes from the Oracle.DataAccess assembly version 2.111.6.20.
db 是 11g，我使用的是 Oracle.DataAccess 程序集版本 2.111.6.20 中的 OracleCommand 和 OracleConnection 类。
I have a data layer in .net which calls an oracle stored procedure. The parameter for the datefield in question is an IN parameter which is marks as a TimeStamp datatype. The field in the table is also defined as a TimeStamp.
我在 .net 中有一个数据层，它调用一个 oracle 存储过程。相关日期字段的参数是一个 IN 参数，它被标记为 TimeStamp 数据类型。表中的字段也定义为时间戳。
// Add the parameter to the OracleCommand
var dt = new DateTime(2011,07,07,10,0,0);
cmd.Parameters.Add("RECEIVED", OracleDbType.TimeStamp, 6, dt, System.Data.ParameterDirection.Input);
cmd.ExecuteNonQuery();
The value of the parameter does contain the time information (10am), but when it gets persisted to the db, the time info is missing.
该参数的值确实包含时间信息（上午 10 点），但是当它被持久化到数据库时，时间信息就会丢失。
My stored procedure looks something like this...
我的存储过程看起来像这样......
create or replace
PROCEDURE DATA_INSERT
(
ID OUT NUMBER
, RECEIVED IN TIMESTAMP
) AS
BEGIN
ID := MY_SEQUENCE.nextval;
INSERT INTO DATA (ID, RECEIVED) VALUES (ID, RECEIVED);
END DATA_INSERT;
Any idea why the timestamp is losing the time information?
知道为什么时间戳会丢失时间信息吗？
回答by vradenburg
Ok, I think I've figured out what the issue is. In the example above I omitted some additional parameters from my stored proc for berevity. I have other parameters in the query, some of which are of datatype DATE. So, consider the following table...
好的，我想我已经弄清楚问题是什么了。在上面的例子中，为了简洁起见，我从我的存储过程中省略了一些额外的参数。我在查询中有其他参数，其中一些是数据类型 DATE。因此，请考虑下表...
CREATE TABLE TEST (DATE_FIELD DATE, TIMESTAMP_FIELD TIMESTAMP);
And the stored procedure...
还有存储过程...
CREATE OR REPLACE PROCEDURE TEST_INSERT ( P_DATE IN DATE, P_TIMESTAMP IN TIMESTAMP ) AS
BEGIN
INSERT INTO TEST(DATE_FIELD, TIMESTAMP_FIELD) VALUES(P_DATE, P_TIMESTAMP);
END TEST_INSERT;
And the following c#...
而下面的c#...
using (var cmd = new OracleCommand("TEST_INSERT", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("P_TIMESTAMP", OracleDbType.TimeStamp
, new DateTime(2011, 07, 07, 10, 0, 0), System.Data.ParameterDirection.Input);
cmd.Parameters.Add("P_DATE", OracleDbType.Date
, new DateTime(2011, 07, 08), System.Data.ParameterDirection.Input);
cmd.ExecuteNonQuery();
}
The result will be...
结果将是...
DATE_FIELD: 11-07-07
TIMESTAMP_FIELD: 11-07-08 00:00:00.000000000
So, even though the parameters are named, it seems as though the name of the parameter is irrelevant and that the order in which parameters are added to the Parameters collection in .net will determine the values of the parameters in the stored procedure.
因此，即使参数被命名，参数的名称似乎也无关紧要，参数添加到 .net 中的 Parameters 集合的顺序将决定存储过程中参数的值。
So, in the c# snippet if you reverse the order in which the parameters are added to the collection to...
因此，在 c# 代码段中，如果您颠倒将参数添加到集合中的顺序以...
cmd.Parameters.Add("P_DATE", OracleDbType.Date
, new DateTime(2011, 07, 08), System.Data.ParameterDirection.Input);
cmd.Parameters.Add("P_TIMESTAMP", OracleDbType.TimeStamp
, new DateTime(2011, 07, 07, 10, 0, 0), System.Data.ParameterDirection.Input);
then the result will be...
那么结果将是...
DATE_FIELD: 11-07-08
TIMESTAMP_FIELD: 11-07-07 10:00:000000000
回答by using System.Noob
If you are using the Oracle provided dll, it binds variables ordinally by default. Add the following line of code before you execute to bind to the names.
如果您使用的是 Oracle 提供的 dll，默认情况下它会按顺序绑定变量。在执行绑定到名称之前添加以下代码行。
cmd.BindByName = true;
回答by DCookie
When you issue this select, what do you see:
当您发出此选择时，您会看到什么：
SELECT TO_CHAR(RECEIVED, 'MM/DD/YYYY HH24:MI:SS') FROM data WHERE ID = <value>;
回答by Harrison
I ran your code into a table and my results were (07-JUL-11 10.00.00.000000000 AM ) which is what is expected. I see that you are using the same name for the column and parameter, try pre-pending a "p_" infront of the variable name and see if it works
我将您的代码运行到表格中，结果是 (07-JUL-11 10.00.00.000000000 AM)，这正是预期的结果。我看到您对列和参数使用了相同的名称，请尝试在变量名称前面添加一个“p_”，看看它是否有效
this is what I ran: In Oracle:
这就是我运行的：在 Oracle 中：
create table t_data(received timestamp);
in c#
在 C# 中
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BEGIN INSERT INTO t_DATA (RECEIVED) VALUES (:RECEIVED); END;",con);
// Add the parameter to the OracleCommand
DateTime dt = new DateTime(2011,07,07,10,0,0);
cmd.Parameters.Add("RECEIVED", OracleDbType.TimeStamp, 6, dt, System.Data.ParameterDirection.Input);
cmd.ExecuteNonQuery();
back to Oracle
回到甲骨文
select * from t_data
RECEIVED
-------------------------
07-JUL-11 10.00.00.000000000 AM
If anything, try something like this to see if it works then go back and check your procedure and see if something doesn't look awry (like a wayward trunc)
如果有的话，尝试这样的事情，看看它是否有效，然后回去检查你的程序，看看是否有什么地方看起来不对（比如一个任性的截断）
