timestamp difference precision and dbms_output.put_line format.

zn553

Hello,

I calculate the difference between two date columns but the results are strange.

this is the script:

set serveroutput on
whenever sqlerror exit sql.sqlcode;
set echo off
SET VERIFY OFF
set heading off
SET FEEDBACK OFF
set serveroutput on size unlimited
set linesize 300
alter session set current_schema = my_schema;
-- alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
ALTER session SET TIME_ZONE='GMT';
Declare
vv_SchemaName VARCHAR2(15);
BEGIN
vv_SchemaName := Sys_Context('USERENV','CURRENT_SCHEMA');
FOR REVOKED IN (
select  a.transaction_id as trans_Id,
        e.eventcode,
        e.eventtime,
        a.time_received,
        (e.eventtime - a.time_received) * 86400 as total
  from      request_history_tbl a 
        join
            eventlog_tbl e
          on a.transaction_id = e.transaction_id
  where e.eventtime >= current_timestamp - 6/24
    and e.eventcode = '51'
    order by 3
    )
    LOOP
    dbms_output.put_line('revocation_request_seconds{' || 'transID=' || '"' || REVOKED.trans_Id || '"' || ',instance=' || '"' || vv_schemaname || '"' || '} ' || REVOKED.total );
    END LOOP;
END;
/

I would like by this to display some metrics for prometheus like this:

revocation_request_seconds{transID="xxxxx",instance="my_schema"} 0

I have two questions if I may:

• is this the best way to add the quotes and curly brackets? and also the schema name?
• the date calculation is not rounded up so i am getting this like:

revocation_request_seconds{transID="xxxxx",instance="my_schema"} .9999999999999999999999999999999999999996

how can i make the script display 1 instead of that large array of 9's ?

thank you

Frank Kulash

Hi, @zn553

Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

is this the best way to add the quotes and curly brackets? and also the schema name?

That depends on what you think is "best". I think it would be better not to concatenate two literals together, so I would do it like this:

dbms_output.put_line ( 'revocation_request_seconds{transID="'
		     || REVOKED.trans_Id
		     || '",instance="'
		     || vv_schemaname
		     || '"} '
		     || REVOKED.total
		     );

how can i make the script display 1 instead of that large array of 9's ?

Display the rounded value, e.g.

ROUND (revoked.total)

which rounds to the nearest integer value, or

ROUND (revoked.total, 6)

which rounds to the nearest multiple of .000001 (that is, 6 decimal places).

BluShadow

You say you want "rounded up" which would imply you actually want CEIL instead of ROUND.

Depends what you want though... you choose ROUND, CEIL or FLOOR...

SQL> ed
Wrote file afiedt.buf


  1  with t(num) as (select .9999999999999999999999996 from dual union all
  2                  select .4999999999999999999999996 from dual union all
  3                  select 1.000000000000000000000001 from dual
  4                 )
  5  select round(num) as round_num
  6        ,ceil(num) as ceil_num
  7        ,floor(num) as floor_num
  8* from t
SQL> /


 ROUND_NUM   CEIL_NUM  FLOOR_NUM
---------- ---------- ----------
         1          1          0
         0          1          0
         1          2          1

mathguy

Within the SELECT statement, wrap the formula for total within round(...)This will cause the result, already converted to seconds, to be rounded to the nearest integer. Round as early as possible, so that you don't carry unneeded information any further.

Frank Kulash

Hi, @zn553

Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

is this the best way to add the quotes and curly brackets? and also the schema name?

That depends on what you think is "best". I think it would be better not to concatenate two literals together, so I would do it like this:

dbms_output.put_line ( 'revocation_request_seconds{transID="'
		     || REVOKED.trans_Id
		     || '",instance="'
		     || vv_schemaname
		     || '"} '
		     || REVOKED.total
		     );

how can i make the script display 1 instead of that large array of 9's ?

Display the rounded value, e.g.

ROUND (revoked.total)

which rounds to the nearest integer value, or

ROUND (revoked.total, 6)

which rounds to the nearest multiple of .000001 (that is, 6 decimal places).

BluShadow

You say you want "rounded up" which would imply you actually want CEIL instead of ROUND.

Depends what you want though... you choose ROUND, CEIL or FLOOR...

SQL> ed
Wrote file afiedt.buf


  1  with t(num) as (select .9999999999999999999999996 from dual union all
  2                  select .4999999999999999999999996 from dual union all
  3                  select 1.000000000000000000000001 from dual
  4                 )
  5  select round(num) as round_num
  6        ,ceil(num) as ceil_num
  7        ,floor(num) as floor_num
  8* from t
SQL> /


 ROUND_NUM   CEIL_NUM  FLOOR_NUM
---------- ---------- ----------
         1          1          0
         0          1          0
         1          2          1