-
SQL query to find difference between two time data
my requirement is :
Time1 :=' 09:12 '
Time2 :=' 18:24'
now kind give me a query to find difference between time1 and time2 in
hour minute and second form.
for ex (2 hrs 35 mins 45 sec)
Thanx in advance
-
I'm not sure you can do this 'nicely' in one query.
Something like this:
SELECT (SYSDATE - (SYSDATE - 0.1)) * 24*60*60
FROM dual;
will get you the interval in seconds. Then something like this:
SELECT TO_CHAR(TRUNC(:time_in_seconds/60/60),'09') ||'hrs'||
TO_CHAR(TRUNC(MOD(:time_in_seconds,3600)/60),'09') ||'mins'||
TO_CHAR(MOD(MOD(:time_in_seconds,3600),60),'09')||'secs'
FROM dual;
...will format. You might consider writing a function for this.
J
-
I have attached one file.
This function will return hours and minutes. e.g. 22:30. You have to modify it as per your requirement.
-
bhattnirav offers an excellent solution.
This achieves the same, but in a different way.
CREATE OR REPLACE FUNCTION j_get_time_interval (p_start_time IN DATE, p_end_time IN DATE)
RETURN VARCHAR2 IS
nTimeIntervalInSeconds NUMBER;
BEGIN
SELECT (p_end_time - p_start_time) * 24*60*60
INTO nTimeIntervalInSeconds
FROM dual;
RETURN TO_CHAR(TRUNC(nTimeIntervalInSeconds/60/60),'09') ||'hrs'||
TO_CHAR(TRUNC(MOD(nTimeIntervalInSeconds,3600)/60),'09') ||'mins'||
TO_CHAR(MOD(MOD(nTimeIntervalInSeconds,3600),60),'09')||'secs';
END j_get_time_interval;
/
-
Hi Binoy,
This will also achieves the same, but in a single query.
SELECT TO_CHAR(TRUNC((( to_date(to_char(sysdate,'DD-MON_YYYY')||' 10:30:00','DD-MON_YYYY HH24:MI:SS' ) -
to_date(to_char(sysdate,'DD-MON_YYYY')||' 09:45:00','DD-MON_YYYY HH24:MI:SS' ) ) * 24*60*60)/60/60),'09') ||':'||
TO_CHAR(TRUNC(MOD((( to_date(to_char(sysdate,'DD-MON_YYYY')||' 10:30:00','DD-MON_YYYY HH24:MI:SS' ) -
to_date(to_char(sysdate,'DD-MON_YYYY')||' 09:45:00','DD-MON_YYYY HH24:MI:SS' ) ) * 24*60*60),3600)/60),'09') ||':'||
TO_CHAR(MOD(MOD((( to_date(to_char(sysdate,'DD-MON_YYYY')||' 10:30:00','DD-MON_YYYY HH24:MI:SS' ) -
to_date(to_char(sysdate,'DD-MON_YYYY')||' 09:45:00','DD-MON_YYYY HH24:MI:SS' ) ) * 24*60*60),3600),60),'09') as "HH:MI:SS"
FROM dual;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|