to_char(interval)

There is no to_char function available for intervals.

Or at least it does not work as expected


SQL> select to_char(interval '1234' second, 'HH24:MM') from dual;
TO_CHAR(INTERVAL'12
-------------------
+00 00:20:34.000000

I just write my own one, with some new format elements

For interval day to second, I have DDD number of days, HH number of hours (0-24), HHH total number of hours (0-99999999999999), etc

here it is


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 9 17:15:59 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> 
SQL> create or replace function tochards(f_int interval day to second,f_fmt varchar2) return varchar2 is
  2  -- valid formats are DDD, HHH, HH, MMM, MM, SSS, SS, FF
  3      ret varchar2(4000);
  4      f varchar2(4000);
  5      i interval day(9) to second(9);
  6  begin
  7      if (f_fmt is null or f_int is null) then
  8          return null;
  9      end if;
 10      f := upper(f_fmt);
 11      if (translate(f,'XDHMSF,.:;/- ','X') is not null) then
 12          raise_application_error(-20001,'Invalid format');
 13      end if;
 14      if (extract(day  from i)<0) then
 15          ret:='-';
 16          i:=f_int*(-1);
 17      else
 18          ret:='';
 19          i:=f_int;
 20      end if;
 21      while (f is not null) loop
 22          if (f like 'DDD%') then
 23              ret:=ret||to_char(extract(day from i),'FM999999999999999999');
 24              f:=substr(f,4);
 25          elsif (f like 'HHH%') then
 26              ret:=ret||to_char(extract(day from i)*24+extract(hour from i),'FM999999999999999999');
 27              f:=substr(f,4);
 28          elsif (f like 'HH%') then
 29              ret:=ret||to_char(extract(hour from i),'FM999999999999999999');
 30              f:=substr(f,3);
 31          elsif (f like 'MMM%') then
 32              ret:=ret||to_char(extract(day from i)*24*60+extract(hour from i)*60+extract(minute from i),'FM999999999999999999');
 33              f:=substr(f,4);
 34          elsif (f like 'MM%') then
 35              ret:=ret||to_char(extract(minute from i),'FM999999999999999999');
 36              f:=substr(f,3);
 37          elsif (f like 'SSS%') then
 38              ret:=ret||to_char(extract(day from i)*24*60*60+extract(hour from i)*60*60+extract(minute from i)*60+trunc(extract(second from i)),'FM999999999999999999');
 39              f:=substr(f,4);
 40          elsif (f like 'SS%') then
 41              ret:=ret||to_char(trunc(extract(second from i)),'FM999999999999999999');
 42              f:=substr(f,3);
 43          elsif (f like 'FF%') then
 44              ret:=ret||to_char(mod(extract(second from i),1),'FM999999999999999999');
 45              f:=substr(f,3);
 46          elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
 47              ret:=ret||substr(f,1,1);
 48              f:=substr(f,2);
 49          else
 50              raise_application_error(-20001,'Invalid format : '||f_fmt);
 51          end if;
 52      end loop;
 53      return ret;
 54  end;
 55  /

Function created.

SQL> 
SQL> sho err
No errors.
SQL> 
SQL> create or replace function tocharym(f_int interval year to month,f_fmt varchar2) return varchar2 is
  2  -- valid formats are YYY, MMM, MM
  3      ret varchar2(4000);
  4      f varchar2(4000);
  5      i interval year to month;
  6  begin
  7      if (f_fmt is null or f_int is null) then
  8          return null;
  9      end if;
 10      f := upper(f_fmt);
 11      if (translate(f,'XYM,.:;/- ','X') is not null) then
 12          raise_application_error(-20001,'Invalid format');
 13      end if;
 14      if (extract(year  from i)<0) then
 15          ret:='-';
 16          i:=f_int*(-1);
 17      else
 18          ret:='';
 19          i:=f_int;
 20      end if;
 21      while (f is not null) loop
 22          if (f like 'YYY%') then
 23              ret:=ret||to_char(extract(year from i),'FM999999999999999999');
 24              f:=substr(f,4);
 25          elsif (f like 'MMM%') then
 26              ret:=ret||to_char(extract(year from i)*12+extract(month from i),'FM999999999999999999');
 27              f:=substr(f,4);
 28          elsif (f like 'MM%') then
 29              ret:=ret||to_char(extract(month from i),'FM999999999999999999');
 30              f:=substr(f,3);
 31          elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
 32              ret:=ret||substr(f,1,1);
 33              f:=substr(f,2);
 34          else
 35              raise_application_error(-20001,'Invalid format : '||f_fmt);
 36          end if;
 37      end loop;
 38      return ret;
 39  end;
 40  /

Function created.

SQL> 
SQL> sho err
No errors.
SQL> 
SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'HHH:MM') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
53561:15

SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'DDD HH:MM:SS') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
2231 17:15:59

SQL> select tocharym(interval '25' month, 'YYY:MM') from dual;

TOCHARYM(INTERVAL'25'MONTH,'YY
------------------------------
2:1

SQL> select tocharym(interval '-25' month, 'MMM') from dual;

TOCHARYM(INTERVAL'-25'MONTH,'M
------------------------------
-25

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options