Home » SQL & PL/SQL » SQL & PL/SQL » I want find how many hour,minutes,seconds is taken from task_stared and task_end. (Oracle 11g)
I want find how many hour,minutes,seconds is taken from task_stared and task_end. [message #632599] Tue, 03 February 2015 08:25 Go to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

I want find how many hour,minutes,seconds is taken from task_stared and task_end.

WITH task_started
     AS (SELECT 'POP_FS_NEW' AS task_id, '2/2/2015 11:33:28 AM' AS task_started FROM DUAL
         UNION
         SELECT 'INDEXING_FS_NEW' AS task_id, '2/2/2015 10:08:02 PM' AS task_started FROM DUAL
         UNION
         SELECT 'STAT_FS_NEW' AS task_id, '2/2/2015 11:45:31 PM' AS task_started FROM DUAL),
     task_complete
     AS (SELECT 'POP_FS_NEW' AS task_id, '2/2/2015 1:09:04 PM' AS task_complete FROM DUAL
         UNION
         SELECT 'INDEXING_FS_NEW' AS task_id, '2/2/2015 11:42:42 PM' AS task_complete FROM DUAL
         UNION
         SELECT 'STAT_FS_NEW' AS task_id, '2/3/2015 10:00:45 AM' AS task_complete FROM DUAL)
SELECT task_started.task_id,
       task_started.task_started,
       task_complete.task_complete
  FROM task_started INNER JOIN task_complete ON task_started.task_id = task_complete.task_id
Re: I want find how many hour,minutes,seconds is taken from task_stared and task_end. [message #632600 is a reply to message #632599] Tue, 03 February 2015 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
INTERVAL = DATE1 - DATE2 -- which can be presented or formatted any way you desire
Re: I want find how many hour,minutes,seconds is taken from task_stared and task_end. [message #632601 is a reply to message #632599] Tue, 03 February 2015 08:32 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Subtract 2 dates and you get the difference in days
multiply that by 24 and you get hours
multiply again by 60 and you get minutes
Re: I want find how many hour,minutes,seconds is taken from task_stared and task_end. [message #632604 is a reply to message #632601] Tue, 03 February 2015 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

... of course, if you have strings instead of dates, it is harder.
What is '2/3/2015'? 2 March or 3 February?

Re: I want find how many hour,minutes,seconds is taken from task_stared and task_end. [message #632613 is a reply to message #632604] Tue, 03 February 2015 10:47 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

No..it's 3rd Feb 2015
Re: I want find how many hour,minutes,seconds is taken from task_stared and task_end. [message #632615 is a reply to message #632613] Tue, 03 February 2015 10:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
na.dharma@gmail.com wrote on Tue, 03 February 2015 22:17
No..it's 3rd Feb 2015


Oh! Can you please tell the same to your database? You might think how?
Re: I want find how many hour,minutes,seconds is taken from task_stared and task_end. [message #632619 is a reply to message #632613] Tue, 03 February 2015 12:55 Go to previous message
EdStevens
Messages: 1377
Registered: September 2013
Senior Member
na.dharma@gmail.com wrote on Tue, 03 February 2015 10:47
No..it's 3rd Feb 2015


It may be to you, but to oracle it's just a character string. Fundamentally no different than "Does anybody really know what time it is? Does anybody really care?"

Do not confuse a DATE with a character string that represents a date.

see: - But I want to store my date as ...
Previous Topic: Wait class snapshot wise using dba_hist_system_event
Next Topic: Convert to date
Goto Forum:
  


Current Time: Thu Aug 27 05:13:43 CDT 2026