Oracle - 从 DATE 数据类型字段中获取以毫秒为单位的日期/时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2343708/
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 - Fetch date/time in milliseconds from DATE datatype field
提问by drake
I have last_update_datecolumn defined as DATEfield
我将last_update_date列定义为DATE字段
I want to get time in milliseconds.
我想以毫秒为单位获得时间。
Currently I have:
目前我有：
TO_CHAR(last_update_date,'YYYY-DD-MM hh:mi:ss am')
But I want to get milliseconds as well.
但我也想获得毫秒。
I googled a bit and think DATEfields will not have milliseconds. only TIMESTAMPfields will.
我用谷歌搜索了一下，认为DATE字段不会有毫秒。只有TIMESTAMP字段会。
Is there any way to get milliseconds? I do not have option to change data type for the field.
有没有办法获得毫秒？我没有更改字段数据类型的选项。
回答by RC.
DATE fields on Oracle only store the data down to a second so there is no way to provide anything more precise than that. If you want more precision, you must use another type such as TIMESTAMP.
Oracle 上的 DATE 字段只能将数据存储到一秒，因此没有办法提供比这更精确的数据。如果您想要更高的精度，则必须使用另一种类型，例如 TIMESTAMP。
Hereis a link to another SO question regarding Oracle date and time precision.
这是另一个关于 Oracle 日期和时间精度的 SO 问题的链接。
回答by rayd09
As RC says, the DATE type only supports a granularity down to the second.
正如 RC 所说，DATE 类型只支持精确到秒的粒度。
If converting to TIMESTAMP is truly not an option then how about the addition of another numerical column that just holds the milliseconds?
如果转换为 TIMESTAMP 确实不是一个选项，那么添加另一个仅包含毫秒的数字列如何？
This option would be more cumbersome to deal with than a TIMESTAMP column but it could be workable if converting the type is not possible.
与 TIMESTAMP 列相比，此选项处理起来更麻烦，但如果无法转换类型，它可能是可行的。
回答by loquin
In a similar situation where I couldn't change the fields in a table, (Couldn't afford to 'break' third party software,) but needed sub-second precision, I added a 1:1 supplemental table, and an after insert trigger on the original table to post the timestamp into the supplemental table.
在我无法更改表中的字段（无法“破坏”第三方软件）但需要亚秒级精度的类似情况下，我添加了一个 1:1 补充表和一个后插入在原始表上触发以将时间戳发布到补充表中。
If you only need to know the ORDER of records being added within the same second, you could do the same thing, only using a sequence as a data source for the supplemental field.
如果您只需要知道在同一秒内添加的记录的 ORDER，您可以做同样的事情，只需使用序列作为补充字段的数据源。
