Query help, date/time calculations

purpendicular · #1 (**permalink**) 02-25-05, 15:03

(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
------------------------------------------------------------------

r937 · #2 (**permalink**) 02-25-05, 19:36

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

purpendicular · #3 (**permalink**) 02-28-05, 06:43

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

Internet Services

Web Development

Digital Marketing

Consumer Tech