SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sum of the differences between 2 datetime fields

    hi everyone

    i'm trying to do some work on finding the sum of the differences between 2 datetime fields in an Oracle (Oracle8) database

    this below obviously doesn't work:
    Code:
    SELECT
     SUM(to_char(time_end,'YYYYMMDDHH24MI') - to_char(time_start,'YYYYMMDDHH24MI'))
    FROM
     mydb.mytbl
    WHERE
     vin=27723
    i need to find the difference in hours between the time_start and time_end fields in each row, and then sum up the total of the differences for all the rows

    Thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    > i need to find the difference in hours between
    > the time_start and time_end fields in each row,
    Code:
    SELECT ( time_end - time_start ) * 24
      FROM mydb.mytbl
     WHERE vin=27723
    > and then sum up the total of the differences for all the rows
    Code:
    SELECT SUM( time_end - time_start ) * 24
      FROM mydb.mytbl
     WHERE vin=27723
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rudy!

    i also just found out that when you subtract 2 DATE fields, you get the difference in the no. of days too... and that solves all (well most... the NT version of SQL Plus isn't all that fun to work with ) my problems


Bookmarks

Posting Permissions

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