Restrict mysql queries to a time range

I have a database that tracks the training modules that a user has read, and the amount of time they have spent viewing the module. Users are not allowed to take a quiz on the subject until they have viewed the training module for the time necessary to read it.

Therefore if a module takes five minutes to read. The user may read for two minutes, go to another section, return and read for another two minutes, leave, and return for the final minute. Each time they leave the module I record the time they spent viewing it.

 I calculate the total time spent  by doing a sum(tutorial_tracking_duration) and the code works,. But I want to restrict the process so that they must have viewed the module for the alloted time (in this case five minutes) within a twenty four hour period. Not the last twenty-four hours, but they must have viewed the training for the allotted time within any twenty-four hour period.

I can't think of a way to do that without a series of temporary tables. Is there an elegant way to do this?

My current code, which does not have the 24 hour restriction, shows below. Note the SUBSTRING_INDEX is because some modules have multiple pages, designated as name_1, name_2 etc. and the SUBSTRING_INDEX strips the _#. $module_series would contain "name"
select SUBSTRING_INDEX(tutorial_tracking_module,'_',-1), SUM(tutorial_tracking_duration) from tutorial_tracking where tutorial_tracking_emp ='$emp' and SUBSTRING_INDEX(tutorial_tracking_module,'_',1)='$module_series'

Open in new window

LVL 6
birwinAsked:
Who is Participating?
 
max-hbConnect With a Mentor Commented:
> In reviewing the database, I see I am also tracking the session. This may be a separate
> question, but how would I restrict the answer to within a session rather than within 24 hours.
> I may be able to insist that the tutorial be taken within a session.
You could probably group your results by session id.

regards
 maxhb
0
 
Adam ChanCommented:
Hi birwin,

You can save a timestamp of when the user starts reading the module(start_time), then count the duration as you normally do. But do a check so if the current time > start_time +24hr, then you reset the duration to zero.
0
 
max-hbCommented:
Hi!
Calculating if the user has read the required time within ANY 24h period is not posible in mysql AFAIK.
If you refione your requirements so that the given time of reading has to be taken within one day, then you may use "group by" to get your results.

This requires that your "tutorial_tracking_duration" has some kind of timestamp on it, eg. "tutorial_tracking_duration_timestamp" as in my example below:




select
SUBSTRING_INDEX(tutorial_tracking_module,'_',-1),
SUM(tutorial_tracking_duration) from tutorial_tracking
where
tutorial_tracking_emp ='$emp'
and
SUBSTRING_INDEX(tutorial_tracking_module,'_',1)='$module_series'
group by 
year(tutorial_tracking_duration_timestamp),
month(tutorial_tracking_duration_timestamp),
day(tutorial_tracking_duration_timestamp)

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
birwinAuthor Commented:
The database holds the following:
tutorial_tracking_id  // auto increment
tutorial_tracking_emp // employee number
tutorial_tracking_duration
tutorial_tracking_session
tutorial_tracking_module  
tutorial_training_timestamp  
addamez: That is an interesting idea, but how would I determine if they had previously fully read the training, and then dropped back to the training module for a few seconds a few days later to verify some fact.
 max-hb: Also an interesting solution, but I can expect that someone might start taking the lesson at 11:45 PM and not finish until 12:15 AM, (some of the training modules take longer than 5 minutes) so I think whatever solution I use has to stick to a clock rather than calendar solution.
In reviewing the database, I see I am also tracking the session. This may be a separate question, but how would I restrict the answer to within a session rather than within 24 hours. I may be able to insist that the tutorial be taken within a session.
0
 
Adam ChanCommented:
> how would I determine if they had previously fully read the training, and then dropped back to the training module for a few seconds a few days later to verify some fact.

A simple true/false flag (i.e. tutorial_tracking_complete) could be set once tutorial_tracking_duration >= the minimum time required has been reached (5min) within the allowed time period (24hrs).

As such, your tracking logic should look something like the pseudo code in the snippet below

As for the session problem, you could store the session along with the true/false flag above to indicate the session in which the user completed the tutorial; or you could set the tutorial_tracking_complete to false when a user leaves a session.
if (current_timestamp - start_timestamp > allowed_time)
  tutorial_tracking_duration = 0;
else if (tutorial_tracking_duration >= alloted_time)
  tutorial_tracking_complete = true;
else
  // nothing happened, keep tracking
 
 
// only allow user to take quiz if the relevant tutorial_tracking_complete is true

Open in new window

0
 
birwinAuthor Commented:
I finally decided to limit the tutorial reading to a single session, and use this code to provide the largest total for any session:
select SUM(tutorial_tracking_duration) as dur  from tutorial_tracking where tutorial_tracking_emp ='$emp' and SUBSTRING_INDEX(tutorial_tracking_module,'_',1)='$module_series' GROUP BY tutorial_tracking_session ORDER BY  dur DESC
The order of the GROUP BY and ORDER BY is important.
0
 
birwinAuthor Commented:
I wanted an all mysql solution. Your suggestion of using GROUP BY for the sessions lead me to ther right answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.