MySQL Time Subtraction

Discussion in 'OT Technology' started by curtis_rak, Mar 1, 2004.

  1. curtis_rak

    curtis_rak Guest

    I need to subtract 2 'TIME' type fields from a MySQL table. They are essentially a 'START' and and 'END'. The differences are always either 1 or 1.5 hours.

    If I simply use a query like (end)-(start) AS Diff, I get a long number (ex. 13000, this might be UNIX time??)

    After reading the MySQL documentation, it looks like I should be using either TIMEDIFF or SUBTIME. However, neither of these seem to work properly.

    I have also tried the following, but it returns '0' for my time difference.
    SELECT sec_to_time(unix_timestamp(end)-unix_timestamp(start)) AS Diff

    Any suggestions?
     
  2. s a x m a n

    s a x m a n New Member

    Joined:
    Nov 16, 2003
    Messages:
    880
    Likes Received:
    0
    are you using the MYSQL time format 00:00:00 for both fields???


    if so, I'll get the query for ya
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
  4. curtis_rak

    curtis_rak Guest


    Yes. There is no date at all, just a time field. (00:00:00)
     
  5. curtis_rak

    curtis_rak Guest

  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    It's at the bottom...
     
  7. curtis_rak

    curtis_rak Guest

    Ahhh, it sure is....and it even worked.

    Thanks man. I will never let my scroll wheell trick me again... :hs:

    This was my final line...
    SELECT sec_to_time(time_to_sec(end)-time_to_sec(start)) AS total_time
     

Share This Page