Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: How to calculate the start time

    I am new to Oracle PL/SQL. I have a question on how to find the start time by using interval. Here is a example:

    I have a END_TIME column which datatype is date (such as '04-29-2004 17:09:55') and a time interval column which data type is varchar2 (such as '02:22:22', which means the duration is 2 hrs 22 mins and 22 secs). Now I want to find out the START_TIME in date format. Does anyone here kindly give me some advices. Thanx,

  2. #2
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52

    Thumbs up

    Hi,

    Is this what your are looking for ?

    SQL> select to_char(sysdate,'dd-mm-yyyy hh:mm:ss') from dual ;

    TO_CHAR(SYSDATE,'DD
    -------------------
    30-04-2004 02:04:09

    SQL>

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    This should do what you want, but I would expect there to be easier ways...

    Code:
    create table dte (end_time date, ival varchar2(8));
    
    insert into dte (end_time, ival) values (to_date('10/04/2004 07:20:00','DD/MM/YYYY HH24:MI:SS'),'03:23:07');
    
    insert into dte (end_time, ival) values (to_date('01/04/2004 17:01:22','DD/MM/YYYY HH24:MI:SS'),'10:01:44');
    
    insert into dte (end_time, ival) values (to_date('20/03/2004 11:30:07','DD/MM/YYYY HH24:MI:SS'),'14:43:23');
    
    select end_time,
           ival,
           end_time- ((1/86400) *( (substr( ival,1,2 )*60*60)+(substr( ival,4,2 )*60)+(substr( ival,7,2 )) )) as start_time        
    from dte
    
    END_TIME		IVAL		START_TIME
    10/04/2004 07:20:00	03:23:07	10/04/2004 03:56:53
    01/04/2004 17:01:22	10:01:44	01/04/2004 06:59:38
    20/03/2004 11:30:07	14:43:23	19/03/2004 20:46:44
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Apr 2004
    Posts
    3
    I find another way to do this is:

    select to_char(end_time, 'MM/DD/YYYY HH24:MIS'), duration_time,
    to_char(server_date - (interval'duration_time' HOUR TO SECOND), 'MM-DD-YYYY HH24:MIS') from <table_name>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •