SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Subtracting 2 time fields

    I'm using MySQL version 5.5.25a

    I need to get the total time difference from startTime and endTime

    So if I have
    startTime = 14:00
    endTime = 15:30

    I need the result to show as 1.5

    I believe I am on the right track just not sure how to show the result as I need it...

    The fields are TIMESTAMP

    Code:
    SELECT
        TIME_FORMAT(SEC_TO_TIME(UNIX_TIMESTAMP(endTime) - UNIX_TIMESTAMP(startTime)), '%H.%i') AS totalTime
    FROM
        table

    Right now my result would be 01.30

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT ROUND(( TIME_TO_SEC(endtime) - 
                   TIME_TO_SEC(starttime) ) / 3600.0 , 2 )  AS hours_diff
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, works great.

    However I failed to mention this when I posted, what do you think is the best way to do this?

    If my timestamp field for startTime is:
    2012-08-01 9:00:00

    and my endTime field is:
    2012-08-02 13:00:00

    What would be the way to have MySQL calculate the time difference between the 2 days, because right now if I use the query it returns: -0.98 instead of 28.0

    Thanks again and sorry for not posting that vital info...

  4. #4
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, for some reason it's working now... lol

    thanks again!


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
  •