CODEWITHSUNDEEP
sqloracle-databasetimestamp
Tushar Jangade
Tushar Jangade

Reputation: 25

convert timestamp into Milliseconds

I am working in the oracle database and I have a timestamp column in which I am supposed to add the current time in it. I am using CURRENT_TIMESTAMP for that. this time is added through the trigger function. The CURRENT_TIMESTAMP adds time in date format which is 2020-10-08 12:28:40.842548. but I want it to be added in the format of milliseconds which is 1533680034852. can you please help me which this thing? Thank you in advance.

Upvotes: 0

Views: 6427

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

I would use a function like this:

create or replace function getTimestamp() return number as
   ds interval day(9) to second(3) := systimestamp - TIMESTAMP '1970-01-01 00:00:00 UTC';
   res number;
begin
   res := EXTRACT(second FROM ds)
          + 60 * EXTRACT(minute FROM ds)
          + 60 * 60 * EXTRACT(hour FROM ds)
          + 60 * 60 * 24* EXTRACT(day FROM ds);
   return 1000 * res;
end;

Upvotes: 1

MT0
MT0

Reputation: 167774

You can use the query:

SELECT   EXTRACT( DAY    FROM difference ) * 24 * 60 * 60 * 1000
       + EXTRACT( HOUR   FROM difference ) *      60 * 60 * 1000
       + EXTRACT( MINUTE FROM difference ) *           60 * 1000
       + EXTRACT( SECOND FROM difference ) *                1000
           AS milliseconds_since_epoch
FROM   (
  SELECT ( SYSTIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 UTC' ) as difference
  FROM   DUAL
);

or:

SELECT ( TRUNC( SYSTIMESTAMP AT TIME ZONE 'UTC', 'MI' ) - DATE '1970-01-01' )
       * 24 * 60 * 60 * 1000
       + EXTRACT( SECOND FROM SYSTIMESTAMP ) * 1000
         AS milliseconds_since_epoch
FROM   DUAL;

Which outputs:

| MILLISECONDS_SINCE_EPOCH |
| -----------------------: |
|        1602709510024.339 |

db<>fiddle here

Upvotes: 1

Vladimir.V.Bvn
Vladimir.V.Bvn

Reputation: 1118

To get CURRENT_TIMESTAMP in milliseconds you can use

select (sysdate - to_date('01-01-1970','DD-MM-YYYY')) * (24 * 60 * 60 * 1000) 
      + to_number(to_char(systimestamp,'FF3')) 
  from dual;

Upvotes: 2

Related Questions