epoch conversion + format to hh:mm:ss

[LGJ]

Hi all,

Please could I have help with getting the difference bettween 2 values from a DB (they are in unix time format) and putting the output in hh:mm:ss.

Below is an eaxmple of what I use to get a value in the format of DD-MON-YYYY on one field:

SELECT TO_CHAR(TO_DATE('01-JAN-1970','DD-MON-YYYY') + ( a.released_t / (60 * 60 * 24) ),'DD-MON-YYYY HH24:MI:SS') "Released Date"
FROM RESERVATION_T a, SERVICE_T b
WHERE a.account_obj_id0 = b.account_obj_id0
AND a.reservation_status = 2;


But I want a.released_t - a.created_t shown in hh:mm:ss

I know how to convert a number directly eg

SELECT TO_CHAR(TO_DATE('1520', 'hh24:mi'), 'hh:mi')
FROM dual

But how can I do this in one step?

Thanks for any help

LGJ

 

[SantaMufasa]

LGJ,

Even though you could do this in SQL-only code, it becomes a bit messy. So, I suggest you use a FUNCTION to calculate the difference between two dates into hours, minutes, and seconds. Here is my code that produces HH:MI:SS differences between two dates over a millenium apart:

Section 1 -- "DateDiff_HHMISS" function code:

create or replace function DateDiff_HHMISS (dt1 date, dt2 date) return varchar2 is
	diffsecstot	number;
	diffhr		number;
	diffmi		number;
	diffss		number;
begin
	diffsecstot	:= abs(dt1-dt2)*24*60*60;
	diffhr		:= trunc(diffsecstot/60/60);
	diffsecstot	:= diffsecstot-(diffhr*60*60);
	diffmi		:= trunc(diffsecstot/60);
	diffss		:= trunc(diffsecstot-(diffmi*60));
	return	lpad(diffhr,6,'0')||':'||lpad(diffmi,2,'0')||':'||
                   lpad(diffss,2,'0');	
end;
/

Section 2 -- Sample invocations:

select DateDiff_HHMISS(sysdate,sysdate-(1/24/60/60))
from dual -- 1 second apart
/

000000:00:01

select DateDiff_HHMISS(sysdate,sysdate-(1141*365+.123))
from dual -- 1,411 years and some hours,minutes, and seconds apart

999516:57:07

Let us know if this is what you needed.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:17 (18Jun04) UTC (aka "GMT" and "Zulu"), 11:17 (18Jun04) Mountain Time)

 

[LGJ]

Thanks for the reply mufasa.

Not really what I wanted as this requires manual input of entries(is this correct)?

I wanted to retrieve the values of say 100 rows and the query to do the retrieval and conversion.

Not sure if this is possible though?

Thanks for the help though.

LGJ

 

[SantaMufasa]

LGJ,

No, not correct ! It just so happens that I generated manual entries in the invocation example, above, but the function certainly does not depend upon manual data entry. Here is an example of my using the function against a standard table query:

select last_name, to_char(start_date,'yyyy-mm-dd')b,
sysdate-start_date c,DateDiff_HHMISS(sysdate,start_date) a
from s_emp
/

                                            Hours
                                       Days Minutes and
                          Hire        Since Seconds
LAST_NAME                 Date         Hire Since Hire
------------------------- ---------- ------ ------------
Velasquez                 1990-03-03  5,225 125400:00:22
Ngao                      1990-03-08  5,220 125288:30:22
Nagayama                  1991-06-17  4,754 114104:30:22
Quick-To-See              1990-04-07  5,190 124568:30:22
Ropeburn                  1990-03-04  5,224 125384:30:22
Urguhart                  1991-01-18  4,904 117704:30:22
Menchu                    1990-05-14  5,153 123680:30:22
Biri                      1990-04-07  5,190 124568:30:22
Catchpole                 1992-02-09  4,517 108416:30:22
Havel                     1991-02-27  4,864 116744:30:22
Magee                     1990-05-14  5,153 123680:30:22
Giljum                    1992-01-18  4,539 108944:30:22
Sedeghi                   1991-02-18  4,873 116960:30:22
Nguyen                    1992-01-22  4,535 108848:30:22
Dumas                     1991-10-09  4,640 111368:30:22
Maduro                    1992-02-07  4,519 108464:30:22
Smith                     1990-03-08  5,220 125288:30:22
Nozaki                    1991-02-09  4,882 117176:30:22
Patel                     1991-08-06  4,704 112904:30:22
Newman                    1991-07-21  4,720 113288:30:22
Markarian                 1991-05-26  4,776 114632:30:22
Chang                     1990-11-30  4,953 118880:30:22
Patel                     1990-10-17  4,997 119936:30:22
Dancs                     1991-03-17  4,846 116312:30:22
Schwartz                  1991-05-09  4,793 115040:30:22

(There is, however, one adjustment to my earlier statement about the "DateDiff_HHMISS" function: as written, its maximum difference between dates is 114 years.)

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:34 (22Jun04) UTC (aka "GMT" and "Zulu"), 08:34 (22Jun04) Mountain Time)