Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    42

    Question Unanswered: How to calculate the difference between 2 dates

    I have 2 columns are datetime field in a table.
    I want to find the differnce of these 2 fields and convert into hours, minutes and seconds.

    For example:

    2004-05-10 17:10:23
    and
    2004-05-05 12:00:00

    The difference should be 125:10:23

    I need to do that in a SQL:
    SELECT .... <-- calculation should be here
    FROM....
    Last edited by ManUtdFans2003; 05-10-04 at 04:20. Reason: Add more information

  2. #2
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    Check out DATE_SUB or DATEDIFF on the MySQL website ... one of these should do the trick:

    http://dev.mysql.com/doc/mysql/en/Da...functions.html
    -Wigz

    ------------------------------------------------
    docendo discimus.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DATE_SUB is not really useful in this situation

    DATEDIFF is not available until 4.1.1, and it deals only in days

    the following works fine, and also allows for dates more than 1 day apart
    Code:
    concat(''
        , case when 
          floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                )  > 0
               then concat(
          floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                ) , ' day'
                  , case when
          floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                )  = 1 then ', ' else 's, ' end
                          )
               else '' end
         , sec_to_time(
           unix_timestamp(date2)
          -unix_timestamp(date1) 
         - floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                ) * 86400  
                )
          ,'.'
          )              as result
      from somedates2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    insert into somedates2 ( date1,date2 ) values
    ( '2004-05-05 12:00:00', '2004-05-10 17:10:23' )

    test result: 5 days, 05:10:23.

    note the hours/minutes/seconds is the output of the SEC_TO_TIME function, and, as such, it's a string

    be aware that SEC_TO_TIME will handle conversions only less than 24 hrs

    if you want to "add" the 5 days back, to make it display as 125:10:23., then you can do that using the SUBSTRING function

    personally, i like to see the days split out
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    42
    Hi,

    Thank you for your code, it works for me !

    However, I get another problem as I have a column is date type and a cloumn is time, how do I combine as a datetime so that I can put it into the date1 in your code ?


    -------------------------------------------------------------------------

    Quote Originally Posted by r937
    DATE_SUB is not really useful in this situation

    DATEDIFF is not available until 4.1.1, and it deals only in days

    the following works fine, and also allows for dates more than 1 day apart
    Code:
    concat(''
        , case when 
          floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                )  > 0
               then concat(
          floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                ) , ' day'
                  , case when
          floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                )  = 1 then ', ' else 's, ' end
                          )
               else '' end
         , sec_to_time(
           unix_timestamp(date2)
          -unix_timestamp(date1) 
         - floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                ) * 86400  
                )
          ,'.'
          )              as result
      from somedates2

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    excuse me, but you said they were datetime columns

    if you have separate date and time columns, why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    42
    This table stores the date and time required for the Job.
    There is another table called JobLog has one field to store the date time of the status of the job.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should run a few tests to see how to combine the date and time values into a datetime value

    i'm not gonna test it for you, sorry, i spent almost an hour working for you for free to come up with the date difference calculations in post #3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    42

    Thumbs up

    Hi,

    I must say thank you for your effort as your code really help me solving the problem.

    I tried the following and found it works to combine date and time field into a single datetime field.

    unix_timestamp(concat(date_format(job.date, '%Y-%m-%d'), ' ', time_format(job.time, '%H:%i')))

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i knew you could do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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