oracle 从 Select 查询中提取完整的时间戳（包括日期）；甲骨文
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6334789/
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
Extract a full timestamp (date included) from a Select query; Oracle
提问by LaDante Riley
So I am trying to insert multiples rows of data from one table to another. I have done this; however, I am having an issue with some of my columns, specifically my date columns. When the query returns data it is missing the time component of the date which is normally present. If this doesn't make sense hopefully the follow helps things to make sense.
所以我试图将多行数据从一个表插入到另一个表。我已经这样做了；但是，我的某些列有问题，特别是我的日期列。当查询返回数据时，它缺少通常存在的日期的时间部分。如果这没有意义，希望下面的内容有助于使事情变得有意义。
My original query
我的原始查询
SELECT 'insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date) values(', SUBCAR,',',BBP.BATCH_ID ,',', SILICON ,',',TEMPERATURE ,',', SULPHUR ,',', MANGANESE ,',', pHOSPHORUS ,',''',START_POUR ,''',''', END_POUR,''',''',SCHED_CAST_DATE ,''');'
FROM bof_chem_sample bcs, bof_batch_pour bbp, bof_celox_sample bofcs
WHERE bcs.SAMPLE_CODE= to_char('D1')
and bofcs.sample_code=bcs.sample_code
and bofcs.batch_id=bcs.batch_id
and bcs.batch_id = bbp.batch_id
and bofcs.temperature>0
AND bbp.START_POUR>=to_date('01-JAN-11')
order by bbp.start_pour
Results of the query:
查询结果：
insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date)
values( 101,65277 ,0.6631,2525 ,0.0551,0.3366,0.043,'01-JAN-11','01-JAN-11','31-DEC-10');
insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date)
values( 123,65277 ,0.6631,2525 ,0.0551,0.3366,0.043,'01-JAN-11','01-JAN-11','31-DEC-10');
insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date)
values( 123,65278 ,0.7116,2470 ,0.0598,0.333,0.0423,'01-JAN-11','01-JAN-11','31-DEC-10');
insert into dante2 (subcar, batch_id, silicon, temperature, sulphur, manganese, phosphorus, start_pour, end_pour, sched_cast_date)
values( 116,65278 ,0.7116,2470 ,0.0598,0.333,0.0423,'01-JAN-11','01-JAN-11','31-DEC-10');
However, I want the dates to look like dd-mon-yy hh24:mi. Does anyone know how to fix this?
但是，我希望日期看起来像 dd-mon-yy hh24:mi。有谁知道如何解决这一问题？
回答by Chandu
Two options:
两种选择：
1) If you have alter session privilege, change the NLS_DATE_FORMAT before running the SELECTstatement as given below:
1) 如果您有修改会话权限，请在运行SELECT语句之前更改 NLS_DATE_FORMAT，如下所示：
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI';
2) If you don't have ALTER session privilege then apply the transformation for each date field as given below (the stmnt below shows it for START_POUR)
2) 如果您没有 ALTER 会话权限，则对每个日期字段应用如下所示的转换（下面的 stmnt 显示它用于 START_POUR）
'TO_DATE(''' || TO_CHAR(START_POUR, 'DD-MON-RR HH24:MI') || ''', ''DD-MON-RR HH24:MI'')'
回答by Mike Meyers
I wonder if you are making this problem more complicated than it needs to be.
我想知道你是否让这个问题比它需要的更复杂。
There is the simple form of INSERTwhere you need need to provide a VALUESclause, but you can also insert the results of a SELECTstatement directly.
有一种简单的形式INSERT，您需要提供 whereVALUES子句，但您也可以SELECT直接插入语句的结果。
So you could just write something like:
所以你可以写一些类似的东西：
insert into dante2 (
subcar, batch_id, silicon, temperature, sulphur, manganese,
phosphorus, start_pour, end_pour, sched_cast_date
)
SELECT
SUBCAR, BBP.BATCH_ID, SILICON, TEMPERATURE, SULPHUR, MANGANESE,
pHOSPHORUS, START_POUR, END_POUR, SCHED_CAST_DATE
FROM
bof_chem_sample bcs,
bof_batch_pour bbp,
bof_celox_sample bofcs
WHERE
bcs.SAMPLE_CODE= to_char('D1') and
bofcs.sample_code=bcs.sample_code and
bofcs.batch_id=bcs.batch_id and
bcs.batch_id = bbp.batch_id and
bofcs.temperature>0 AND
bbp.START_POUR>=to_date('01-JAN-2011', 'dd-mon-yyyy');
This will have the advantage of being much faster than your existing solution since each of the INSERT statements that you were running would take time to parse and execute but this has only single statement to run. It also runs in one step since you don't need to create the INSERT statements and all the types (like dates) are handled correctly.
这将具有比您现有的解决方案快得多的优势，因为您正在运行的每个 INSERT 语句都需要时间来解析和执行，但这只有一个语句要运行。它还可以一步运行，因为您不需要创建 INSERT 语句并且所有类型（如日期）都得到了正确处理。
However if you still need to create insert statements then you need to handle the data types. Strings and numbers are fine because Oracle knows what to do with them. dates are more of an issue because unless you use the to_charand to_datefunctions then you are relying on implicit conversion which will cause you mysterious problems like the ones you are experiencing. It's probably worth reading the Oracle documentation on implicit conversionto find out why it is bad.
但是，如果您仍然需要创建插入语句，那么您需要处理数据类型。字符串和数字很好，因为 Oracle 知道如何处理它们。日期更像是一个问题，因为除非您使用to_char和to_date函数，否则您将依赖于隐式转换，这将导致您遇到像您遇到的那些神秘问题。可能值得阅读有关隐式转换的Oracle 文档以找出它为什么不好。
As already mentioned by Cybernate, you would need to explicitly convert the date/time columns firstly to a character string (using to_char) that can then be converted back to a date by using to_date.
正如 Cybernate 已经提到的，您需要首先将日期/时间列显式转换为字符串（使用 to_char），然后可以使用 to_date 将其转换回日期。
回答by James
First, do you have some sample data of the date fields?
首先，您是否有一些日期字段的示例数据？
If the information about the timestamp isn't stored it will do little good to call it.
如果没有存储有关时间戳的信息，那么调用它就没什么用了。
The main issue I would look into it the format of the source you're pulling from.
我将研究的主要问题是您从中提取的源的格式。
From there you can set the format of the SELECT statement's output.
从那里您可以设置 SELECT 语句输出的格式。
