How to extract millisecond from date in Oracle?

Question

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.252 i want this answer= 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.

score 10 · Accepted Answer · answered Mar 16 '16 at 08:44

10

i have full date (with time)

This can only be done using a timestamp. Although Oracle's date does contain a time, it only stores seconds, not milliseconds.

To get the fractional seconds from a timestamp use to_char() and convert that to a number:

select to_number(to_char(timestamp '2016-03-16 10:45:04.252', 'FF3'))
from dual;

answered Mar 16 '16 at 08:44

Thanks you for efforts.i got my answer – Nikunj Chavda Dec 12 '19 at 10:37
This is the cleanest answer. – Seymour Apr 09 '20 at 14:30
What about `EXTRACT( timestamp_value, MILLISECOND )` ? (**EDIT**: Ah, that's only in "Oracle Big Data", not Oracle Database, even in version 21c, that's crazy...) – Dai Feb 18 '21 at 10:48
I noticed that this works in Oracle 11g: `SELECT ( timestamp_value - CAST( ValidFrom AS timestamp(0) ) ) AS ms FROM tableName` however the type of `ms` is still `timestamp(n)`. – Dai Feb 18 '21 at 10:55

score 0 · Answer 2 · edited Nov 14 '19 at 12:08

0

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

edited Nov 14 '19 at 12:08

Dharman

30,962
25
85
135

answered Nov 14 '19 at 12:05

AhmedAboElMagd

1

1

code only answers, while being correct, are not really useful to understand the solution. Please consider adding some text explaining what you have done and what the code means – John Doe Nov 14 '19 at 12:13

score 0 · Answer 3 · edited Mar 30 '23 at 01:11

0

The millisecond of the day:

  select to_number(to_char(tms_now, 'sssss') || to_char(tms_now, 'ff3')) day_millisecond
    from (select systimestamp as tms_now from dual);

The microsecond of the day:

  select to_number(to_char(tms_now, 'sssss') || to_char(tms_now, 'ff')) day_microsecond
    from (select systimestamp as tms_now from dual);

edited Mar 30 '23 at 01:11

user16217248

3,119
19
19
37

answered Mar 24 '23 at 15:27

Ibrahim_Al_Sabath

1

How to extract millisecond from date in Oracle?

3 Answers3