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
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
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.
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