PL SQL：如何显示时间戳的纳秒
我有两个时间戳，即 start= 15-03-11 15:10:10 和 finish=15-03-11 15:10:10 我需要减去它们（“finish-start”）才能找到持续时间。因为它们的差异是纳秒级别的，所以我需要将它们分别转换为 15:10:10.000000001 和 15:10:10.000000015。
I have two timestamps i.e. start= 15-03-11 15:10:10 and finish=15-03-11 15:10:10 and i need to subtract them ("finish-start" ) in order to find the duration. Because their difference is in nanosecond level, i need to convert them into 15:10:10.000000001 and 15:10:10.000000015 respectively.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
取决于它们的存储方式 - TIMESTAMP 列的默认设置仅保留 6 位小数 Oracle 参考，因此必须将字段声明为 TIMESTAMP(9)，如果是这样，则还需要将差异声明为 间隔(9)
Depends on how they have been stored - the default setting for TIMESTAMP columns only holds to 6 decimal places Oracle reference so the fields have to be declared TIMESTAMP(9) and if so then the difference will also need to be declared as INTERVAL(9)
版本、平台
首先，它与您的版本和服务器平台有关。（阅读 OMG Ponies 说明）。
为什么？
版本？
如果您使用的版本早于 Oracle 9i，您别无选择，只能使用“Date”数据类型。而且您无法以十亿分之一秒的语言进行本机管理。
服务器平台？
时间来自操作系统。如果您的操作系统管理数十亿秒，您将能够以十亿分之一的粒度管理“时间戳”。
示例：
Windows xp 或 Vista 不允许让您管理十亿分之一秒。
数据类型
如果您的数据库平台管理十亿秒（正如马克已经指出的），您可以使用以下数据类型：TIMESTAMP (9) 和INTERVAL（时间量）间隔天到秒(9)。
Version , platform
First of all, it's about your version and your server platform.(Read OMG Ponies note).
Why?
Version?
If you're using a version older than Oracle 9i, you have no choice but to use "Date" datatype.And you can't manage natively in the language billionth of seconds.
Server platform?
The time comes from the operating system. If your OS manage billions of seconds you'll be able to manage "Timestamp" with a billionth of granularity.
Esamples:
Windows xp or Vista don't let you manage a billionth of second.
Datatypes
If your db platform manages billionth of seconds(as Mark has already pointed out), you can use as datatypes: TIMESTAMP (9) and INTERVAL(quantity of time) Interval day to seconds(9).
为了补充 Zep、Mark 和 OMG Ponies 的答案 - 当然，如果这些值是从其他来源（例如科学设备）加载的，其提供的精度比您的操作系统/Oracle 版本支持的精度更高，那么您可以存储并按照您的意愿操纵这些值。
例如，将它们存储为 varchar2 并编写您自己的转换例程。如果您必须走这条路，请注意您的计算需要考虑的事项，例如日展期等。
To supplement the answers from Zep, Mark, and OMG Ponies - of course, if these values are loaded from some other source (e.g. scientific equipment) that provides a higher degree of precision than what your OS / Oracle version supports, then you can store and manipulate these values as you wish.
For example, store them as a varchar2 and write your own converstion routines. If you have to go that route, be mindfull of things like day rollover etc that your calculation would need to account for.
这对我有用。这个想法是将差异转换为字符串，并应用正则表达式来检索时间的适当部分，并将该部分转换为纳秒，最后将所有部分相加。我只记录了秒（没有记录小时和分钟），但你明白了。
/* Formatted on 10/5/2012 5:35:08 PM (QP5 v5.149.1003.31008) */ SELECT TO_NUMBER ( REGEXP_SUBSTR (TO_CHAR (systimestamp - sysdate), '[0-9]{2}', 14), '99') * 1000000000 + TO_NUMBER ( REGEXP_SUBSTR (TO_CHAR ( (systimestamp - sysdate) ), '[0-9]{6}', 14), '999999') FROM dualThis worked for me. The idea is to convert the difference to a string and apply regex to retrieve the appropriate parts of the time and convert that part to nanoseconds and at the end add up all the parts. I've done only the seconds (not the hour and minute), but you get the idea.
/* Formatted on 10/5/2012 5:35:08 PM (QP5 v5.149.1003.31008) */ SELECT TO_NUMBER ( REGEXP_SUBSTR (TO_CHAR (systimestamp - sysdate), '[0-9]{2}', 14), '99') * 1000000000 + TO_NUMBER ( REGEXP_SUBSTR (TO_CHAR ( (systimestamp - sysdate) ), '[0-9]{6}', 14), '999999') FROM dual