SQL 如何从Oracle中的日期中提取毫秒？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36030420/
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 to extract millisecond from date in Oracle?
提问by Nikunj Chavda
i have full date(with time). But i want only millisecond from date.
我有完整的日期（有时间）。但我只想要从日期开始的毫秒数。
please tell me one line solution
请告诉我一行解决方案
for example: date= 2016/03/16 10:45:04.252i want this answer= 252
例如：date= 2016/03/16 10:45:04.252我想要这个答案=252
i try to use this query.
我尝试使用此查询。
SELECT ADD_MONTHS(millisecond, -datepart('2016/03/16 10:45:04.252', millisecond),
'2016/03/16 10:45:04.252') FROM DUAL;
but i'm not success.
但我没有成功。
回答by a_horse_with_no_name
i have full date (with time)
我有完整的日期（有时间）
This can only be done using a timestamp. Although Oracle's datedoes contain a time, it only stores seconds, not milliseconds.
这只能使用timestamp. 尽管 Oracledate确实包含时间，但它只存储秒，而不是毫秒。
To get the fractional seconds from a timestampuse to_char()and convert that to a number:
要从时间戳使用小数秒to_char()并将其转换为数字：
select to_number(to_char(timestamp '2016-03-16 10:45:04.252', 'FF3'))
from dual;
回答by AhmedAboElMagd
SELECT
TRUNC((L.OUT_TIME-L.IN_TIME)*24) ||':'||
TRUNC((L.OUT_TIME-L.IN_TIME)*24*60) ||':'||
ROUND(CASE WHEN ((L.OUT_TIME-L.IN_TIME)*24*60*60)>60 THEN ((L.OUT_TIME-L.IN_TIME)*24*60*60)-60 ELSE ((L.OUT_TIME-L.IN_TIME)*24*60*60) END ,5) Elapsed
FROM XYZ_TABLE
