Home » SQL & PL/SQL » SQL & PL/SQL » date function
date function [message #218978] Mon, 12 February 2007 06:10 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
hello sir,

i have done a query to get the exact duration of time.
please go through my query and suggest me what i will do next.

SQL> ed
Wrote file afiedt.buf

  1  select ((to_char(time_out, 'hh')-to_char(time, 'hh'))*3600+
  2  ( to_char(time_out, 'mi')-to_char(time, 'mi'))*60+
  3  to_char(time_out, 'ss')-to_char(time, 'ss'))/3600"hour"
  4* from tim
SQL> /

      hour
----------
11.1894444
8.47305556
8.22305556
6.25638889
5.33972222

5 rows selected.


till now i got difference beteen them in hour, how could i get minutes, seconds between them.


my table structure is
SQL> SELECT * FROM TIM;

NAME         TIME                 TIME_OUT
------------ -------------------- --------------------
ashish       01-feb-2007 01:23:00 01-feb-2007 12:34:22
ashish       01-feb-2007 01:45:00 01-feb-2007 22:13:23
raju         01-feb-2007 01:20:00 01-feb-2007 21:33:23
ashish       01-feb-2007 01:28:00 01-feb-2007 19:43:23
raju         01-feb-2007 04:23:00 01-feb-2007 21:43:23

5 rows selected.

regards

[Updated on: Mon, 12 February 2007 06:12]

Report message to a moderator

Re: date function [message #218996 is a reply to message #218978] Mon, 12 February 2007 07:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Search
By
Vamsi
Re: date function [message #219072 is a reply to message #218978] Mon, 12 February 2007 16:52 Go to previous message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
Hi,

Is this is what you meant. Do you need the difference in hours,minutes,seconds (all same). If so you have the answer in your query.


select hour, hour * 60 as min, hour * 60 * 60 as sec from
(
select
(to_char(time_out, 'hh')-to_char(time, 'hh'))*3600+
(to_char(time_out, 'mi')-to_char(time, 'mi'))*60+
(to_char(time_out, 'ss')-to_char(time, 'ss'))/3600 as hour
from tim
);

Thats it.

or if you want the answer like 11 hours , 12 mins & 30 secs for example then use the following query

select floor(hour) as hour, floor(mod(hour,1) * 60) as min, floor(mod(mod(hour,1) * 60,1) * 60) as sec from
(
select
(to_char(time_out, 'hh')-to_char(time, 'hh'))*3600+
(to_char(time_out, 'mi')-to_char(time, 'mi'))*60+
(to_char(time_out, 'ss')-to_char(time, 'ss'))/3600 as hour
from tim
);

Thanks
Panneer Selvam
Previous Topic: referential integrity constraint question
Next Topic: PLSQL Packages
Goto Forum:
  


Current Time: Wed Dec 07 16:24:49 CST 2016

Total time taken to generate the page: 0.12699 seconds