Home » SQL & PL/SQL » SQL & PL/SQL » how to get two different dates difference in day/hour/min/sec please (oracle 9i)
how to get two different dates difference in day/hour/min/sec please [message #419090] Wed, 19 August 2009 16:44 Go to next message
mohini7@live.com
Messages: 3
Registered: August 2009
Junior Member
Hello All,

First Of all Greetings to ALL,

in DoctorChart table i have DocName,Id#,Citcotype,Medtype,AdmitDate,DischargeDate(both admitdate & Discharge date is date formatted columns)

so how can i get the below differenceTime (which means Dischargedate-Admit Date in below format i.e, Day,Hours,Minutes,Seconds...)


DoctorName, ID #, Citco type, MEDtype, DifferenceTime
========= === ======= ======= ===========
Dr. KindaEmesko, 20045, Replace OutCard, ICH, 0 day 9 hours 11 minutes
Dr. KindaEmesko, 20098, Replace OutCard, ICH, 1 day 2 hours 34 minutes
Dr. KindaEmesko, 20678, Replace OutCard, ICH, 2 day 23 hours 52 minutes
Dr. KindaEmesko, 20212, Replace OutCard, ICH, 4 day 1 hours 00 minutes
Dr. KindaEmesko, 20345, Replace OutCard, BED, 3 days 14 hours 15 minutes
Dr. KindaEmesko, 20678, Replace OutCard, BED, 9 days 21 hours 52 minutes
Dr. KindaEmesko, 20015, Signature Overlay, Rest, 0 days 3 hours 29 minutes
Dr. KindaEmesko, 45678, Signature Overlay, Rest, 0 days 1 hours 29 minutes


Same way how can i get the Average Time for the above result (without ID columns)

DoctorName, Citco type, MEDtype, Avg DifferenceTime
========= === ======= ======= ===========
Dr. KindaEmesko, Replace OutCard, ICH, 2 day 1 hours 00 minutes
Dr. KindaEmesko, Replace OutCard, BED, 6 days 17 hours 15 minutes
Dr. KindaEmesko, Signature Overlay, Rest, 0 days 2 hours 29 minutes


Please just tell me how to get the two dates difference as day/hour/minute format
as well as how to get avg ( two dates difference as day/hour/minute format)

Please Help me

Thanks in advance
Re: how to get two different dates difference in day/hour/min/sec please [message #419092 is a reply to message #419090] Wed, 19 August 2009 17:00 Go to previous messageGo to next message
mohini7@live.com
Messages: 3
Registered: August 2009
Junior Member
By using the below

TO_CHAR(TRUNC((c.DischargeDate-c.AdmitDate))) || ' Days, ' ||
TO_CHAR(TRUNC(MOD((c.DischargeDate-c.AdmitDate)*24,24))) || ' Hours, ' ||
TO_CHAR(TRUNC(MOD((c.DischargeDate-c.AdmitDate)*24*60,60))) || ' Minutes' ||
TO_CHAR(TRUNC(MOD((c.DischargeDate-c.AdmitDate)*24*60*60,60))) || ' Secs' as twocol

i got the difference

but how can i get the average difference

i tried the below one but i got same as above result

TO_CHAR(TRUNC(AVG(c.DischargeDate-c.AdmitDate))) || ' Days, ' ||
TO_CHAR(TRUNC(MOD(AVG(c.DischargeDate-c.AdmitDate)*24,24))) || ' Hours, ' ||
TO_CHAR(TRUNC(MOD(AVG(c.DischargeDate-c.AdmitDate)*24*60,60))) || ' Minutes' ||
TO_CHAR(TRUNC(MOD(AVG(c.DischargeDate-c.AdmitDate)*24*60*60,60))) || ' Secs' as twocol


Please help me
Kind regards
mohini
Re: how to get two different dates difference in day/hour/min/sec please [message #419093 is a reply to message #419092] Wed, 19 August 2009 17:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The things you posted look OK to me, so the problem must be in the things you didn't post.

Did you add a "group by" clause to the second query?

And please read the Forum Guide on how to format your posts (especially the code) so that it is better readable.
Re: how to get two different dates difference in day/hour/min/sec please [message #419101 is a reply to message #419093] Wed, 19 August 2009 17:50 Go to previous messageGo to next message
mohini7@live.com
Messages: 3
Registered: August 2009
Junior Member
Thanks thomas

i have a little problem in group by(unnecessarily i am grouping those discharge date, admin date along with other group by columns)

Thanks for your Help

Thanks to all

i Great ful to you all
Re: how to get two different dates difference in day/hour/min/sec please [message #419108 is a reply to message #419101] Wed, 19 August 2009 19:05 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SQL> drop   table holder ;

Table dropped.

SQL> 
SQL> create table holder (
  2  	 beg_date timestamp,
  3  	 end_date timestamp)
  4  	 /

Table created.

SQL> 
SQL> INSERT INTO HOLDER VALUES(to_timestamp('2009-07-16:19:00:01','YYYY-MM-DD:HH24:MI:SS'),
  2  			       to_timestamp('2009-08-17:20:00:59','YYYY-MM-DD:HH24:MI:SS'));

1 row created.

SQL> INSERT INTO HOLDER VALUES(to_timestamp('2009-08-16:19:00:01','YYYY-MM-DD:HH24:MI:SS'),
  2  			       to_timestamp('2009-08-17:20:00:45','YYYY-MM-DD:HH24:MI:SS'));

1 row created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> select
  2  to_number(extract(day from (END_DATE-BEG_DATE)))	 days,
  3  to_number(extract(hour from (END_DATE-BEG_DATE)))	 hours,
  4  to_number(extract(minute from (END_DATE-BEG_DATE))) minutes,
  5  to_number(extract(second from (END_DATE-BEG_DATE))) seconds
  6  from holder
  7  /

      DAYS	HOURS	 MINUTES    SECONDS
---------- ---------- ---------- ----------
	32	    1	       0	 58
	 1	    1	       0	 44

SQL> 
SQL> select
  2  (
  3  to_number(extract(second from (END_DATE-BEG_DATE)))+
  4  to_number(extract(minute from (END_DATE-BEG_DATE)))*60+
  5  to_number(extract(hour from (END_DATE-BEG_DATE)))*60*60+
  6  to_number(extract(day from (END_DATE-BEG_DATE)))*60*60*24
  7  ) DELTA
  8  from holder
  9  /

     DELTA
----------
   2768458
     90044

SQL> 
SQL> select
  2  avg(
  3  to_number(extract(second from (END_DATE-BEG_DATE)))+
  4  to_number(extract(minute from (END_DATE-BEG_DATE)))*60+
  5  to_number(extract(hour from (END_DATE-BEG_DATE)))*60*60+
  6  to_number(extract(day from (END_DATE-BEG_DATE)))*60*60*24
  7  ) AVG_DELTA
  8  from holder
  9  /

 AVG_DELTA
----------
   1429251
Previous Topic: Strange behavior using function in SQL and PL/SQL
Next Topic: Need an Query idea (merged 3)
Goto Forum:
  


Current Time: Sun Dec 11 00:07:13 CST 2016

Total time taken to generate the page: 0.06149 seconds