SQL Oracle 时间戳数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8987975/
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 data type
提问by hudi
what is the different between timestamp data type without parameter and with parameter 0:
不带参数和带参数 0 的时间戳数据类型有什么区别：
timestampVS timestamp(0)
timestampVS timestamp(0)
回答by Michael Berkowski
The number in parentheses specifies the precision of fractional seconds to be stored. So, (0)would mean don't store any fraction of a second, and use only whole seconds. The default value if unspecified is 6 digits after the decimal separator.
括号中的数字指定要存储的小数秒的精度。因此，(0)这意味着不要存储任何一秒的时间，而只使用整秒。如果未指定，则默认值是小数点分隔符后的 6 位数字。
So an unspecified value would store a date like:
因此，未指定的值将存储如下日期：
TIMESTAMP 24-JAN-2012 08.00.05.993847 AM
And specifying (0)stores only:
并(0)仅指定商店：
TIMESTAMP(0) 24-JAN-2012 08.00.05 AM
回答by APC
Quite simply the number is the precision of the timestamp, the fraction of a second held in the column:
很简单，数字是时间戳的精度，列中保存的秒数：
SQL> create table t23
2 (ts0 timestamp(0)
3 , ts3 timestamp(3)
4 , ts6 timestamp(6)
5 )
6 /
Table created.
SQL> insert into t23 values (systimestamp, systimestamp, systimestamp)
2 /
1 row created.
SQL> select * from t23
2 /
TS0
---------------------------------------------------------------------------
TS3
---------------------------------------------------------------------------
TS6
---------------------------------------------------------------------------
24-JAN-12 05.57.12 AM
24-JAN-12 05.57.12.003 AM
24-JAN-12 05.57.12.002648 AM
SQL>
If we don't specify a precision then the timestamp defaults to six places.
如果我们不指定精度，则时间戳默认为六位。
SQL> alter table t23 add ts_def timestamp;
Table altered.
SQL> update t23
2 set ts_def = systimestamp
3 /
1 row updated.
SQL> select * from t23
2 /
TS0
---------------------------------------------------------------------------
TS3
---------------------------------------------------------------------------
TS6
---------------------------------------------------------------------------
TS_DEF
---------------------------------------------------------------------------
24-JAN-12 05.57.12 AM
24-JAN-12 05.57.12.003 AM
24-JAN-12 05.57.12.002648 AM
24-JAN-12 05.59.27.293305 AM
SQL>
Note that I'm running on Linux so my TIMESTAMPcolumn actually gives me precision to six places i.e. microseconds. This would also be the case on most (all?) flavours of Unix. On Windows the limit is three places i.e. milliseconds. (Is this still true of the most modern flavours of Windows - citation needed).
请注意，我在 Linux 上运行，所以我的TIMESTAMP专栏实际上给了我六个位置的精度，即微秒。大多数（所有？）Unix 版本也是如此。在 Windows 上，限制是三个位置，即毫秒。（这是否仍然适用于最现代的 Windows 版本 - 需要引用）。
As might be expected, the documentation covers this. Find out more.
正如所料，文档涵盖了这一点。 了解更多。
"when you create timestamp(9) this gives you nanos right"
“当您创建时间戳（9）时，这将为您提供 nanos 权利”
Only if the OS supports it. As you can see, my OEL appliance does not:
仅当操作系统支持它时。如您所见，我的 OEL 设备不会：
SQL> alter table t23 add ts_nano timestamp(9)
2 /
Table altered.
SQL> update t23 set ts_nano = systimestamp(9)
2 /
1 row updated.
SQL> select * from t23
2 /
TS0
---------------------------------------------------------------------------
TS3
---------------------------------------------------------------------------
TS6
---------------------------------------------------------------------------
TS_DEF
---------------------------------------------------------------------------
TS_NANO
---------------------------------------------------------------------------
24-JAN-12 05.57.12 AM
24-JAN-12 05.57.12.003 AM
24-JAN-12 05.57.12.002648 AM
24-JAN-12 05.59.27.293305 AM
24-JAN-12 08.28.03.990557000 AM
SQL>
(Those trailing zeroes could be a coincidence but they aren't.)
（那些尾随零可能是巧合，但事实并非如此。）
