oracle PL SQL：如何显示时间戳的纳秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5318082/
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
PL SQL :How to show nanoseconds of a TIMESTAMP
提问by veg123
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.
我有两个时间戳，即 start= 15-03-11 15:10:10 和 finish=15-03-11 15:10:10 ，我需要减去它们（“finish-start”）才能找到持续时间。因为它们的差异在纳秒级，所以我需要将它们分别转换为 15:10:10.000000001 和 15:10:10.000000015。
回答by user151019
Depends on how they have been stored - the default setting for TIMESTAMP columns only holds to 6 decimal places Oracle referenceso the fields have to be declared TIMESTAMP(9) and if so then the difference will also need to be declared as INTERVAL(9)
取决于它们的存储方式 - TIMESTAMP 列的默认设置仅保留 6 个小数位Oracle 引用，因此必须将字段声明为 TIMESTAMP(9)，如果是这样，则差异也需要声明为INTERVAL(9)
回答by zep
Version , platform
版本，平台
First of all, it's about your version and your server platform.(Read OMG Ponies note). Why?
首先，这与您的版本和服务器平台有关。（阅读 OMG Ponies 笔记）。 为什么？
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 billionthof granularity.
版本？如果您使用的版本早于 Oracle 9i，您别无选择，只能使用“日期”数据类型。而且您无法在十亿分之一秒的语言中进行本地管理。
服务器平台? 时间来自于操作系统。如果您的操作系统管理数十亿秒，您将能够以十亿分之一的粒度管理“时间戳” 。
Esamples: Windows xp or Vista don'tlet you manage a billionth of second.
示例：Windows xp 或 Vista不会让您管理十亿分之一秒。
Datatypes
数据类型
If your db platform managesbillionth of seconds(as Mark has already pointed out), you can use as datatypes: TIMESTAMP (9) and INTERVAL(quantityof time) Interval day to seconds(9).
如果您的数据库平台管理十亿分之一秒（正如马克已经指出的那样），您可以将其用作数据类型：TIMESTAMP (9) 和 INTERVAL(时间量) Interval day to seconds(9)。
回答by Michael Broughton
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.
为了补充 Zep、Mark 和 OMG Ponies 的答案——当然，如果这些值是从提供比您的 OS/Oracle 版本支持的精度更高的精度的其他来源（例如科学设备）加载的，那么您可以存储并根据需要操纵这些值。
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.
例如，将它们存储为 varchar2 并编写您自己的转换例程。如果您必须走那条路线，请注意您的计算需要考虑的诸如日间翻转等事情。
回答by fidesachates
This 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
