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

3 thoughts on “to_char(interval)”

  1. You can influence the display to some extent with CAST:

    WITH t AS (SELECT INTERVAL ’2′ SECOND AS i FROM dual)
    SELECT CAST(i AS INTERVAL DAY(0) TO SECOND(0)) AS formatted
    FROM t

    FORMATTED
    ——————-
    +0 00:00:02

  2. select to_char(interval ’1234′ second) from dual;
    is sort of okay, giving 20 Minutes and 34 seconds. With your first SQL, it seemed to ignore the format mask.
    Couldn’t see anything in the docs that says there even is a TO_CHAR for the interval date format.

  3. i did the formatting this way:

    WITH t AS (SELECT INTERVAL ’2′ SECOND AS i FROM dual)
    SELECT trim(to_char(extract(HOUR from i), ’00′))
    || ‘:’ || trim(to_char(extract(MINUTE from i), ’00′))
    || ‘:’ || trim(to_char(extract(SECOND from i), ’00′)) AS formatted
    FROM t

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>