TIMESTAMP(6) Oracle 插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9755556/
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
TIMESTAMP(6) Oracle Insert
提问by David Garcia
I am generating dummy data for a database, one attribute is using TIMESTAMP(6).
我正在为数据库生成虚拟数据，一个属性正在使用TIMESTAMP(6).
Can anyone give me an example of how the value looks?
谁能给我一个例子来说明价值的样子？
Also I would like to be able to insert the date along
我也希望能够插入日期
i.e 03/18/2012 02:35 AM
IE 03/18/2012 02:35 AM
so that it looks like
所以它看起来像
INSERT INTO FLIGHT VALUES (1,'London Heathrow','Miami Airport','03/18/2012 02:35 AM','04/18/2012 13:35 PM');
Is this possible? Thanks alot...
这可能吗？非常感谢...
EDIT----
编辑 - -
How can i get rid of the timestamp column massive space
我怎样才能摆脱时间戳列的大量空间
回答by Justin Cave
Whenever you are dealing with DATEor TIMESTAMPdata types, you should always insert DATEor TIMESTAMPvalues. You should not be inserting strings and relying on implicit conversions to convert the string to a DATEor a TIMESTAMP. You should be explicitly calling TO_DATEor TO_TIMESTAMP. Your INSERTstatement should also be explicitly listing the names of the columns you're inserting into.
每当您处理DATE或TIMESTAMP数据类型时，您应该始终插入DATE或TIMESTAMP值。您不应该插入字符串并依赖隐式转换将字符串转换为 aDATE或 a TIMESTAMP。您应该明确调用TO_DATEor TO_TIMESTAMP。您的INSERT语句还应该明确列出您要插入的列的名称。
You'd want your INSERTstatement to look something like this
你希望你的INSERT语句看起来像这样
INSERT INTO FLIGHT( <<list of columns>> )
VALUES (1,
'London Heathrow',
'Miami Airport',
to_timestamp( '03/18/2012 02:35 AM', 'MM/DD/YYYY HH:MI AM'),
to_timestamp( '04/18/2012 13:35 PM', 'MM/DD/YYYY HH:MI AM') );
You can adjust how SQL*Plus displays the data in a particular column by changing the format mask. For E.g.
您可以通过更改格式掩码来调整 SQL*Plus 显示特定列中数据的方式。例如
SQL> column dept_time format a30;
SQL> column arrv_time format a30;
will cause SQL*Plus to display both DEPT_TIMEand ARRV_TIMEin 30 horizontal characters (your current NLS_TIMESTAMP_FORMATappears to generate 28 character strings, if you change your NLS_TIMESTAMP_FORMAT, you may want to change the width of the column you're asking SQL*Plus to display).
将导致 SQL*Plus 以30 个水平字符显示DEPT_TIME和ARRV_TIME（您当前NLS_TIMESTAMP_FORMAT似乎生成 28 个字符串，如果您更改您的NLS_TIMESTAMP_FORMAT，您可能希望更改您要求 SQL*Plus 显示的列的宽度）。
回答by reto
Please take a look at the following example:
请看下面的例子：
ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';
SELECT *
FROM orders
WHERE ordered_at
BETWEEN '2013-10-06 12:00:00'
AND '2013-10-06 16:00:00'
