如何使用 TIMESTAMP 数据类型减少 Oracle (SQL Plus) 中的列宽？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16217728/
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
How can I reduce the width of column in Oracle (SQL Plus) with TIMESTAMP datatype?
提问by Conor Ryan
In SQL Plus output it is taking up much more space than it needs and I'd like to reduce it from say 50 chars to 20.
在 SQL Plus 输出中，它占用的空间比它需要的多得多，我想将它从 50 个字符减少到 20 个字符。
回答by Ed Gibbs
If you reduce the width to 20 it won't be wide enough for the default TIMESTAMPor TIMESTAMP WITH TIME ZONEformat. When that happens, SQLPlus will wrap the value.
如果您将宽度减小到 20，则它对于默认值TIMESTAMP或TIMESTAMP WITH TIME ZONE格式来说不够宽。发生这种情况时，SQLPlus 将包装该值。
Assume table bhas timestamp column TS:
假设表b有时间戳列TS：
COLUMN ts FORMAT A20
SELECT ts FROM b;
TS
--------------------
25-APR-13 11.28.40.1
50000 AM
To cut down the width even further, decide which information you want and format accordingly. The Oracle DateTime and Timestamp formatting codes are listed here.
要进一步缩小宽度，请确定所需的信息并相应地设置格式。此处列出了Oracle 日期时间和时间戳记格式代码。
Note that SQLPlus won't let you specify a date format with the COLUMNstatement. That's why I used FORMAT A20above. You can get it to 19 characters if you drop fractional seconds and use a 24-hour clock instead of AM/PM, and drop the time zone:
请注意，SQLPlus 不允许您在COLUMN语句中指定日期格式。这就是我FORMAT A20上面使用的原因。如果删除小数秒并使用 24 小时制而不是 AM/PM 并删除时区，则可以将其设置为 19 个字符：
COLUMN TSFormatted FORMAT A20
SELECT TO_CHAR(ts, 'MM/DD/YYYY HH24:MI:SS') AS TSFormatted FROM b;
TSFORMATTED
--------------------
04/25/2013 11:28:40
If you're willing to drop the century you can get two of the fractional seconds and an exact width of 20:
如果您愿意放弃世纪，您可以获得两个小数秒和 20 的确切宽度：
COLUMN TSFormatted FORMAT A20
SELECT TO_CHAR(ts, 'MM/DD/YY HH24:MI:SS.FF2') AS TSFormatted from b;
TSFORMATTED
--------------------
04/25/13 11:28:40.15
Finally, if you want all of your timestamps to be automatically formatted in a certain way, use ALTER SESSION:
最后，如果您希望以某种方式自动格式化所有时间戳，请使用ALTER SESSION：
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'MM/DD/YY HH24:MI:SS.FF2';
COLUMN ts FORMAT A20
SELECT ts from b; -- don't need to_char because of the default format
TS
--------------------
04/25/13 11:28:40.15
回答by marceljg
SUBSTR function will work someof the time,once you have converted the timestamp (ie. to_CHAR) but specifying a column in sqlplus gives you more control:
SUBSTR 函数在某些时候会起作用，一旦您转换了时间戳（即 to_CHAR），但在 sqlplus 中指定一列可以为您提供更多控制：
eg:
例如：
COLUMN SALARY FORMAT $99,990
列薪格式 $99,990
Source:
来源：
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch6.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch6.htm
I say someof the time for substr, as I have seen sqlplus cut off the output, but then still pad the output up to the defined size of the column.
我说substr 的一些时间，因为我已经看到 sqlplus 切断了输出，但仍然将输出填充到列的定义大小。
