# Thread: Query help, date/time calculations

1. Registered User
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?
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. SQL Consultant
Join Date
Apr 2002
Location
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')
, interval 1 day))
as event_duration_percentage
from tbl_dt_event
where dt_downtime_planned = 'yesorno'
and dt_event_initiated >= 'startdate'
, 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.

3. Registered User
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
•