Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Unanswered: Calculating the difference in minutes

    I've been searching through the messages and haven't found any that really hit what I'm looking to do.
    Below you can see the attached image of my input screen (along with the calendar pop-up I'm using for this field entry).

    I want to be able to retrieve in minutes the difference between the time an even was resolved and the time it started.
    This will be used in periodic downtime reporting calculations.

    Some events will be only a few hours, some could last for more than a 24 hour period.

    What must I do to make this work. date_diff seems to only give whole day results, and a few of the other functions seem to get close to what I'm looking for, but not exactly. I'm a novice here and not really a coder (yet... as this project goes on I'm learning more and more), so I could use some guidance here.
    Attached Thumbnails Attached Thumbnails date-time_diff.JPG  

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    select eventid
         , unix_timestamp(resolved)      as resolved_seconds
         , unix_timestamp(started)       as started_seconds
         , ( unix_timestamp(resolved)
           - unix_timestamp(started))/60 as difference_in_minutes
      from yourtable | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    excellent, thanks so much for that... looks like it works like a charm!!!
    now I'm on to figure how to convert the minutes to hour:minutes to display for the user. aka this ticket was open for 36 hours 14 minutes, but I should be able to work this one out
    Last edited by purpendicular; 02-18-05 at 11:17. Reason: .

  4. #4
    Join Date
    Mar 2004
    Here's the code I used, I was successful in grabbing seconds, minutes, hours and day differences from selected records.
    This SQL may help people who are looking for similar information and are trying to find out how to do things like
    calculate minutes, seconds, hours, days

    I was using the advanced pane in the recordset creator within Dreamweaver MX 2004 for this.

    were the fields that held the datetime fields in the table
    that I'm working with below (fyi)

    --SQL Starts here---
    SELECT * ,
    (unix_timestamp(tbl_dt_event.dt_event_diffused) - unix_timestamp(tbl_dt_event.dt_event_initiated)) as difference_in_seconds ,

    (unix_timestamp(tbl_dt_event.dt_event_diffused) - unix_timestamp(tbl_dt_event.dt_event_initiated)) /60 as difference_in_minutes,

    (unix_timestamp(tbl_dt_event.dt_event_diffused) - unix_timestamp(tbl_dt_event.dt_event_initiated)) /3600 as difference_in_hours,

    (unix_timestamp(tbl_dt_event.dt_event_diffused) - unix_timestamp(tbl_dt_event.dt_event_initiated)) /3600/24 as difference_in_days

    FROM tbl_dt_event
    ORDER BY dt_event_id DESC
    --SQL ends here--

Posting Permissions

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