SQL Oracle 默认时间戳格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25245541/
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 default Timestamp format
提问by Bibin Zacharias
I just set default timestamp format as
我只是将默认时间戳格式设置为
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'
When I insert the data into the table the timestamp inserted as,
当我将数据插入表中时，时间戳插入为，
0014-08-11 04:45:24.000000000
When I query
当我查询
SELECT SYSTIMESTAMP FROM DUAL
I get:
我得到：
11-AUG-14 06.14.58.400000000 PM +04:00
But I want the default timestamp as 2014-07-22 05:54:18.000000000.
但我希望默认时间戳为 2014-07-22 05:54:18.000000000。
It would be appreciated if some one could help me on this.
如果有人可以帮助我，我将不胜感激。
回答by Alex Poole
SYSTIMESTAMPreturns data type TIMESTAMP WITH TIMEZONE. So you either need to cast it to a plain TIMESTAMP:
SYSTIMESTAMP返回数据类型TIMESTAMP WITH TIMEZONE。所以你要么需要将它转换为一个普通的TIMESTAMP：
SELECT CAST(SYSTIMESTAMP AS TIMESTAMP) FROM DUAL;
CAST(SYSTIMESTAMPASTIMESTAMP)
-----------------------------
2014-08-11 15:27:11.091862000
Or set the separate NLS_TIMESTAMP_TZ_FORMATparameter:
或者设置单独的NLS_TIMESTAMP_TZ_FORMAT参数：
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
----------------------
2014-08-11 15:27:11.35
1526000
Either way you're losing the time zone information, which may or may not matter to you.
无论哪种方式，您都会丢失时区信息，这对您来说可能无关紧要。
But this doesn't have anything to do with querying values from a TIMESTAMP(without time zone) column in your table. The value in the table has no format incidentally; the NLS settings when you insert will not affect how it is stored or how it is displayed when queried. You need to specify the format at query time as well as at insert time - and preferably using explicit format models with TO_TIMESTAMP()and TO_CHAR()rather than replying on NLS settings, which you might not be able to control.
但这与从表中的TIMESTAMP（无时区）列查询值没有任何关系。顺便说一下，表中的值没有格式；插入时的 NLS 设置不会影响它的存储方式或查询时的显示方式。你需要在查询时，以及在插入时，以指定的格式-并且最好使用显式格式机型TO_TIMESTAMP()和TO_CHAR()，而不是回答关于NLS设置，您可能无法控制。
You should also be using HH24since you no longer have the AM/PM marker.
您也应该使用，HH24因为您不再有 AM/PM 标记。
回答by Pramod
Convert TIMESTAMP format in Oracle DB
在 Oracle DB 中转换 TIMESTAMP 格式
By setting nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6';in init.ora file and restart the instance.
通过设置nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6'; 在 init.ora 文件中并重新启动实例。
