OraFAQ Forum: SQL & PL/SQL » Time Difference Between two Time

Home » SQL & PL/SQL » SQL & PL/SQL » Time Difference Between two Time

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Time Difference Between two Time [message #626652]

Thu, 30 October 2014 06:00

Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi

Member

Hi All,

i want to calculate two time difference like this

Time1==> 03-JAN-2014 08:15:00

Time2==> 03-JAN-2014 17:00:00

Answer is ==> 03-JAN-2014 08:45:00

How can it perform

Regard

Nasir Azeem

Report message to a moderator

Re: Time Difference Between two Time [message #626657 is a reply to message #626652]

Thu, 30 October 2014 06:09

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The difference between 2 datetimes is an interval of time or a number of seconds, it CAN'T be a date.
When you are asked "how old are you?", do you answer "1st January 1495 at 8 o'clock"?

Explain how datetime2-datetime1 can be a new datetime.

Report message to a moderator

Re: Time Difference Between two Time [message #626658 is a reply to message #626652]

Thu, 30 October 2014 06:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Answer is ==> 03-JAN-2014 08:45:00
WRONG!

Difference between two DATES is an INTERVAL in unit of days; without any date.

  1  select to_date('03-JAN-2014 17:00:00','DD-MON-YYYY HH24:MI:SS')-
  2* TO_DATE('03-JAN-2014 08:15:00','DD-MON-YYYY HH24:MI:SS') DAYS FROM DUAL
SQL> /

      DAYS
----------
.364583333

Report message to a moderator

Re: Time Difference Between two Time [message #626661 is a reply to message #626657]

Thu, 30 October 2014 06:19

Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi

Member

as like office time_in and office time_out .

time_in = 09:00:00
time_out = 17:30:00

now i need total working hour with in a date.

Report message to a moderator

Re: Time Difference Between two Time [message #626662 is a reply to message #626661]

Thu, 30 October 2014 06:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>now i need total working hour with in a date.

what date should be in answer when time_in = 23:00 & time_out = 07:30?

Which part of my previous response do you NOT understand????????????????????

Report message to a moderator

Re: Time Difference Between two Time [message #626663 is a reply to message #626661]

Thu, 30 October 2014 06:27

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As BlackSwan showed you:

SQL> with 
  2    data as (
  3      select TO_DATE('03-JAN-2014 08:15:00','DD-MON-YYYY HH24:MI:SS') time_in,
  4             to_date('03-JAN-2014 17:00:00','DD-MON-YYYY HH24:MI:SS') time_out
  5      from dual
  6    )
  7  select time_out-time_in work_time_in_days,
  8         (time_out-time_in)*24 work_time_in_hours,
  9         (time_out-time_in)*1440 work_time_in_minutes,
 10         (time_out-time_in)*86400 work_time_in_seconds
 11  from data
 12  /
WORK_TIME_IN_DAYS WORK_TIME_IN_HOURS WORK_TIME_IN_MINUTES WORK_TIME_IN_SECONDS
----------------- ------------------ -------------------- --------------------
       .364583333               8.75                  525                31500

Report message to a moderator

Re: Time Difference Between two Time [message #626664 is a reply to message #626663]

Thu, 30 October 2014 06:30

Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi

Member

Thank you sir i understand for your code.

Report message to a moderator

Previous Topic:	How to use PL/SQL Collection Type in TABLE function(how to convert PL/SQL type to SQL Object Type)
Next Topic:	Calling Shell script from pl/sql procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 16:49:57 CDT 2024