CODEWITHSUNDEEP
oracle-databaseplsqloracle11gtriggerstimestamp
Miraie
Miraie

Reputation: 13

Oracle current_timestamp to second including milliseconds

This is my first time posting here. I've been looking out for all threads here & other websites but still I'm unable to fix the codes.

Here it is, I have a sample table called LM Where the structure looks like this: (Cannot change table structure)

LM_REFID(PK)  LM_DESC
----------------------------------
VARCHAR2(50)  VARCHAR2(50)

And LM_REFID is the PRIMARY KEY

So, here the things, I want to create a BEFORE INSERT trigger to assign timestamp values in seconds, including the milliseconds. It has to be counted in seconds (with milliseconds) from current time to 1/1/1970 00:00:00 (db time)

(As considering the simultaneous insert would violates PK due to same seconds values. Hence, I want the milliseconds values so the PK is unique)

The trigger looks like this:

CREATE OR REPLACE TRIGGER LM_TRG
BEFORE INSERT ON LM
REFERENCING NEW AS NEW 
FOR EACH ROW
BEGIN
:new.LM_REFID := (SYSDATE - TO_DATE('01/01/1970 00:00:00','MM-DD-YYYY HH24:MI:SS'))
* 24 * 60 * 60 ;
END;
/

This would result to values returned in SECONDS is 1449677554

But I don't want the SECONDS only, I want the milliseconds too, so the PK is unique. So, I've tried replaced the SYSDATE function with timestamp there like:

:new.LM_REFID:= (CURRENT_TIMESTAMP - TO_TIMESTAMP ('01/01/1970 00:00:00',
'MM-DD-YYYY HH24:MI:SSFF3')) * 24 * 60 * 60 * 1000

But it leads to error. I also have tried others like CAST, EXTRACT, epoch and more, but still no luck.

I want the sample data for example should be looked like this :

LM_REFID           
----------------------------
1449677554.123456
1449677554.123344
1444677554.124466

Anyone can help to improve the trigger? Thank you.

Upvotes: 1

Views: 944

Answers (1)

user330315
user330315

Reputation:

Maybe something like this: 

declare
   l_interval interval day(9) to second(6);
   l_seconds  number(24,6);
begin
  l_interval := current_timestamp - timestamp '1970-01-01 00:00:00.000000';
  l_seconds := extract(day from l_interval) * 24 * 60 * 60 + 
               extract(hour from l_interval) * 60 * 60 + 
               extract(minute from l_interval) * 60 + 
               extract(second from l_interval); 
  dbms_output.put_line(l_seconds);  
end;
/

l_seconds will contain fractional seconds as well, that's why it has to be declared as number with decimal digits.

But I never get more than three decimal digits out of this - don't know why.

Upvotes: 1

Related Questions