Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    55

    Unanswered: Query help, date/time calculations

    (Before I get started, are there any books you can refer me to in learning this stuff (aside form the MySQL Manual)?)

    My situation:
    I'm trying to add new functionality to an existing working query.
    Below is my current query,
    It is based on an event that has taken place.
    Checks to see if it was planned or unplanned and
    spits out the results within a date range, also
    calculating the period of time each event lasted.

    What I'm trying to add is:
    1. sum all the periods of time from each event (total_allevents)
    2. calculate the difference in time between ('startdate') and ('enddate') giving the ('time_period')
    3. finally divide the ('total_allevents') / ('time_period') * 100 giving the ('event_duration_percentage') within the timeframe that the user has selected.

    Also, on a scale of 1-10, how complicated is what I'm trying to do?
    Thanks for your guidance,
    Ray

    ------------------------------------------------------------------
    SELECT * , (unix_timestamp(tbl_dt_event.dt_event_diffused) - unix_timestamp(tbl_dt_event.dt_event_initiated)) as event_duration
    FROM tbl_dt_event
    WHERE tbl_dt_event.dt_downtime_planned = 'yesorno' AND tbl_dt_event.dt_event_initiated >= 'startdate' AND tbl_dt_event.dt_event_initiated < date_add('enddate' , interval 1 day)
    ORDER BY tbl_dt_event.dt_event_id ASC
    ------------------------------------------------------------------
    Last edited by purpendicular; 02-25-05 at 17:34.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the best teacher is practice -- answer as many questions on database forums as you can

    or, if you can't answer them (or can't answer them in time before someone else does), you should at least attempt them, and this means setting up the scenario, if you can, on your own database and trying to write the sql on your own

    Code:
    select sec_to_time(
           sum(
            unix_timestamp(dt_event_diffused)
           -unix_timestamp(dt_event_initiated)
              )       )  as total_allevents
         , 100 *
           sum(
            unix_timestamp(dt_event_diffused)
           -unix_timestamp(dt_event_initiated)
              )
           / ( unix_timestamp('startdate')
              -unix_timestamp(date_add('enddate'
                                     , interval 1 day)) 
                 as event_duration_percentage    
      from tbl_dt_event
     where dt_downtime_planned = 'yesorno' 
       and dt_event_initiated >= 'startdate' 
       and dt_event_initiated < date_add('enddate'
                                       , interval 1 day)
    on a scale of 1 to 10, this one was maybe a 5

    but then, i've been writing sql for about 18 years
    Last edited by r937; 02-25-05 at 20:38.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    55
    I'm going to try this code some day this week, right now I'm covering 3rd shift for a vacationer. zzzzzzzzz
    Thanks for taking the time...
    Ray

Posting Permissions

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