Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: calculate total time

    Hi All,

    i have a question;
    i have a table say table1.
    it has columns as start_time and end_time.
    i have to fetch total_time ( which is start_time - end_time )

    there is no column as total_time

    can anyone please suggest me how do I write a sql query
    to calculate total_time.

    thanks
    Mark

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this depends on which database you're using

    standard sql is probably not what you really want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2005
    Posts
    92

    calculate total time

    Hi,

    the database that i am using is oracle.
    the table is: table1
    columns: start_time and end_time

    i have to calculate total_time ( which will be start_time - end_time) using the columns in the table.

    thanks
    mark

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moved to oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2005
    Posts
    92

    calculate total time

    Hi All,

    the datatype for start_time and end_time is NUMBER.

    thanks
    mark

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I won't go into why a timestamp is stored as a number instead as a date. However,

    select end_time - start_time total_time
    from my_table;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What format is the time stored in? Is 1:13 PM stored as 1313, or 13.13 or 113? How far apart are the times for the worst case? Minutes apart? Hours? Days?

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    is the NUMBER the JAVA epoch timestamp?? (seconds from some epoch)
    example: right now it is: 1163452979
    http://www.esqsoft.com/javascript_ex...e-to-epoch.htm

    as mentioned by Bill -
    you need END_TIME minus START_TIME

    start_time - end_time won't give you anything worthwhile
    (well, maybe the negative of the proper time take perhaps)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or is it Julian date?

  10. #10
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by beilstwh
    I won't go into why a timestamp is stored as a number instead as a date.
    Despite the fact that not storing a date as a date is a constant source of trouble everafter, I won't either .

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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