CODEWITHSUNDEEP
oracle-databasetimestamp
Angelina
Angelina

Reputation: 2265

Format timestamp output

I have COMPLETION_DATETIME and START_DATETIME of type: timestamp.

SELECT (COMPLETION_DATETIME - START_DATETIME) INTO model_load_time FROM "COUNT_STATISTICS" where "MODEL" = todays_model;

Above query returns: 

XCOMPASS loaded in: +000000000 05:19:24.180022

How do I get rid of leading zeros?

Upvotes: 0

Views: 252

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

The difference between two timestamps is an interval, and you can't format those as easily as dates and timestamps - you can't use to_char, for example. I'm assuming this is in a PL/SQL block, model_load_time is declared as varchar2, and you're using dbms_output to display.

In which case you can use a regular expression to remove the leading +/- and the zeros from the days portion, while leaving the time portion intact:

regexp_replace(model_load_time, '^[+-]?[0]*[ ]?'));

As a test:

set serveroutput on
declare
  model_load_time varchar2(40);
begin
  /*
  SELECT (COMPLETION_DATETIME - START_DATETIME)
  INTO model_load_time
  FROM "COUNT_STATISTICS"
  where "MODEL" = todays_model;
  */

  -- dummy to get same interval
  select systimestamp - (systimestamp
    - numtodsinterval((60*60*5) + (60*19) + 24.180022, 'SECOND'))
  into model_load_time
  from dual;

  dbms_output.put_line('XCOMPASS loaded in: ' || model_load_time);
  dbms_output.put_line('XCOMPASS loaded in: '
    || regexp_replace(model_load_time, '^[+-]?[0]*'));
  dbms_output.put_line('XCOMPASS loaded in: '
    || regexp_replace(model_load_time, '^[+-]?[0]*[ ]?'));
end;
/

Produces:

anonymous block completed
XCOMPASS loaded in: +000000000 05:19:24.180022000
XCOMPASS loaded in:  05:19:24.180022000
XCOMPASS loaded in: 05:19:24.180022000

This preserves the leading zero on the 'hours' number; one version also preserves the space that is normally between the number of days and the hours but I suspect you don't want that really. If the interval is more than a day then it will still show the number of days, without a leading zero:

XCOMPASS loaded in: 1 05:19:24.180022000

I think it's safe to assume that your completion will never be before your start so you don't need to worry about negative intervals. If you were sure the interval would never be more than a day you could use substr instead:

  dbms_output.put_line('XCOMPASS loaded in: ' || substr(model_load_time, 12));

... but I think the regex is safer?

The other way to approach this is with extract() but that's a bit more long-winded; it does give you more control though, and lets you show values greater than a day as 30 hours, for example.

Upvotes: 2

cdummy
cdummy

Reputation: 455

Check this one

SELECT substr(CAST((systimestamp - systimestamp)
               AS INTERVAL DAY(1) TO SECOND(3)),4) Result
  FROM dual;

Upvotes: 1

Related Questions