Solved

Hours count if over x minutes of hour

Posted on 2014-02-04
15
361 Views
Last Modified: 2014-02-27
I need to insert a record if the user was logged in for more than 30 mins of the hour for each hour of the day. I'm looking for the SQL of how to make this happen.

Here's the data which has the login & logout fields that I need to parse and append into a new table.
User_ID	LoginDt	LogoutDt
user1	2/3/2014 9:02:03 AM	2/3/2014 10:54:36 AM
user1	2/3/2014 10:56:35 AM	2/3/2014 11:39:37 AM
user1	2/3/2014 11:46:16 AM	2/3/2014 2:08:35 PM
user1	2/3/2014 2:09:38 PM	2/3/2014 3:07:33 PM
superuser22	2/3/2014 12:59:48 PM	2/3/2014 4:14:58 PM
superuser22	2/3/2014 4:17:37 PM	2/3/2014 5:47:39 PM
superuser22	2/3/2014 5:50:48 PM	2/3/2014 7:39:39 PM
superuser22	2/3/2014 7:43:32 PM	2/3/2014 8:10:59 PM
userX	2/3/2014 10:24:29 AM	2/3/2014 10:24:40 AM
userX	2/3/2014 10:55:32 AM	2/3/2014 10:55:44 AM

Open in new window


Here's the target table I'm trying to populate which should look something like the below:

UserID	Date	Hour	LoginCount
user1	2/3/14	9	1
user1	2/3/14	10	1
user1	2/3/14	11	1
user1	2/3/14	12	1
user1	2/3/14	13	1
user1	2/3/14	14	1
superuser22	2/3/14	13	1
superuser22	2/3/14	14	1
superuser22	2/3/14	15	1
superuser22	2/3/14	16	1
superuser22	2/3/14	17	1
superuser22	2/3/14	18	1
superuser22	2/3/14	19	1

Open in new window

0
Comment
Question by:Dalexan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 39833930
Looks like you're going to need some sort of processing loop to process each line of the input:

Pseudo code might look something like this:
While Input to read
  Get User, inTime, outTime
  if( outTime - inTime > 30 minutes )
    if( HourOf(inTime) = HourOf(outTime) )
      insert User, DateOf(inTime), HourOf(inTime), 1
    else
      if( MinuteOf(inTime) <= 30 )
        insert User, DateOf(inTime), HourOf(inTime), 1
      for( Each Hour between (HourOf(inTime), HourOf(outTime) )
        insert User, DateOf(inTime), Hour, 1
      if( MinuteOf(outTime) >= 30 )
        insert User, DateOf(inTime), HourOf(outTime), 1
Next Line

The situation would need to be further expanded if you have to handle the possibility that dates change while they are logged in.
0
 

Author Comment

by:Dalexan
ID: 39833944
Yes, dates change where the user is logged in through midnight...
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 39835568
Then the pseudo code only needs the following changes:

While Input to read
  Get User, inTime, outTime
  if( outTime - inTime > 30 minutes )
    if( HourOf(inTime) = HourOf(outTime) )
      insert User, DateOf(inTime), HourOf(inTime), 1
    else
      if( MinuteOf(inTime) <= 30 )
        insert User, DateOf(inTime), HourOf(inTime), 1
      for( Each Hour between (HourOf(inTime), HourOf(outTime) )
        insert User, DateOf(Hour), Hour, 1
      if( MinuteOf(outTime) >= 30 )
        insert User, DateOf(outTime), HourOf(outTime), 1
Next Line
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Dalexan
ID: 39835696
Thanks, I meant to ask in my question if you can you show the sql for this. I don't think there's a for loop structure available in mysql. I want to put this into a stored proc that runs on the server.
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 39841992
As you can see, I can figure out the logic... but I'm no SQL expert.  I know how to use SQL to query and modify a database.  But I don't know how to implement this logic in just SQL... given that SQL is a way to interact with a database and NOT a programming language, I don't think it is possible to implement this sort of logic into something as limiting as SQL... but then again, I'm not enough of a SQL expert to say it's impossible.
0
 

Author Comment

by:Dalexan
ID: 39842030
Thanks HooKooDooKu but I'm really looking for some help with the SQL of how to make this happen. SQL has some programmatic structures: Mysql flow control statements and I really want this to run as a stored proc on the server.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39846608
You might consider using PHP to do part of the work.  It's more suited to DATETIME manipulation that MySQL (See AntiPractice #12).  The knowledge base for handling DATETIME in PHP and MySQL is available in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

If you're interested in a PHP solution, please post back and I'll try to show you how the code might work.
0
 

Author Comment

by:Dalexan
ID: 39850456
I looked through the link, please show the php that might work to append the calculated result to the target table.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39851337
I can spend a little time on this today, and I'll try to show you the logic for handling the DATETIME values.  Before you go too far down this road, consider your data source and quality carefully.  It's nearly impossible to know how long a user is "logged in" if you're dealing with a stateless client/server protocol.  Some of the reasons for that are described here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/A_11271-Understanding-Client-Server-Protocols-and-Web-Applications.html

Another reason, that I should probably add to the article is that once the response is complete, the server cannot know what the (human) client is doing.  Am I reading the computer screen or walking the dog?  No way to tell.
0
 

Author Comment

by:Dalexan
ID: 39851648
The data source is from a client/server app which is inserting to the DB upon button click of login or logout. This data is consistent and used for other reports. This is part of a commissions based CRM system and the report will be used to value the performance of the user based on logged in time per hour, its to the users benefit to be at the computer screen. Thanks for being thorough and detail oriented in your responses.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39854635
Please see http://www.laprbass.com/RAY_temp_dalexan.php

<?php // RAY_temp_dalexan.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Database/MySQL/Q_28356371.html

// THE TEST DATA SET (SLIGHTLY MODIFIED - SEE user2)
$str = <<<EOD
User_ID    LoginDt    LogoutDt
user1    2/3/2014 9:02:03 AM    2/3/2014 10:54:36 AM
user1    2/3/2014 10:56:35 AM    2/3/2014 11:39:37 AM
user1    2/3/2014 11:46:16 AM    2/3/2014 2:08:35 PM
user1    2/3/2014 2:09:38 PM    2/3/2014 3:07:33 PM
user2    2/3/2014 4:04 PM    2/3/2014 4:30 PM
user2    2/3/2014 4:35 PM    2/3/2014 4:45 PM
superuser22    2/3/2014 12:59:48 PM    2/3/2014 4:14:58 PM
superuser22    2/3/2014 4:17:37 PM    2/3/2014 5:47:39 PM
superuser22    2/3/2014 5:50:48 PM    2/3/2014 7:39:39 PM
superuser22    2/3/2014 7:43:32 PM    2/3/2014 8:10:59 PM
userX    2/3/2014 10:24:29 AM    2/3/2014 10:24:40 AM
userX    2/3/2014 10:55:32 AM    2/3/2014 10:55:44 AM
EOD;


// SET THE TIMEZONE TO GREENWICH
date_default_timezone_set('UTC');


// MAKE AN ARRAY FROM THE LINES AND DISCARD THE TOP ROW
$arr = explode(PHP_EOL, $str);
unset($arr[0]);


// MAKE AN ARRAY OF OBJECTS
$datas = array();
$users = array();
foreach ($arr as $str)
{
    // BREAK EACH LINE ON TAB CHARACTERS
    $sub = explode("\t", $str);

    // SAVE THE USER ID ONLY ONCE
    $users[$sub[0]] = $sub[0];

    // COLLECT THE HOURS WORKED
    $datas[] = new Datex($sub[0], $sub[1], $sub[2]);
}


// AGGREGATE THE DATA FROM THE OBJECTS INTO ONE FOR EACH USER
foreach ($users as $user)
{
    $aggrs[$user] = new Aggregate($user, $datas);
}


// USE THE AGGREGATED TIMES FOR EACH USER TO PRODUCE THE STATEMENTS
foreach ($aggrs as $user => $aggr)
{
    foreach ($aggr->hours as $timex => $secs)
    {
        $date = date('Y-m-d', strtotime($timex));
        $hour = date('H',     strtotime($timex));
        echo PHP_EOL . "$user $date $hour 1";
    }
}


Class Datex
{
    public $user, $hours;
    public function __construct($user, $alpha, $omega)
    {
        $this->user  = $user;
        $this->hours = array();

        // MAKE TIMESTAMPS FROM THE PERIOD
        $ta = strtotime($alpha);
        $tz = strtotime($omega);

        // GET INITIAL HOUR BOUNDARIES
        $ha = date('Y-m-d H:00:00', $ta);
        $hz = date('Y-m-d H:00:00', $tz);

        // DO THESE TIMES OCCUR WITHIN THE SAME HOUR?
        if ($ha == $hz)
        {
            $secs = $tz - $ta;
            $this->hours[$ha] = $secs;
            return TRUE;
        }

        // IF TIMES ARE SPREAD ACROSS MORE THAN ONE HOUR
        while ($ha < $hz)
        {
            // GET THE STARTING POINT OF THE NEXT HOUR
            $ha1 = date('Y-m-d H:00:00', strtotime($ha . ' + 1 HOUR'));

            // GET THE NUMBER OF SECONDS IN THIS HOUR
            $secs = strtotime($ha1) - $ta;
            $this->hours[$ha] = $secs;

            // BUMP THE HOUR AND THE STARTING TIMESTAMP
            $ha = $ha1;
            $ta = strtotime($ha);
        }

        // AT THE END, TAKE THE PART OF THE LAST HOUR
        $secs = $tz - strtotime($hz);
        $this->hours[$hz] = $secs;
    }
}


Class Aggregate
{
    public function __construct($user, $arr)
    {
        // INPUT IS THE ARRAY OF DATEX OBJECTS
        foreach ($arr as $obj)
        {
            // AGGREGATION MATCHING USERS
            if ($obj->user == $user)
            {
                // FIRST ENTRY - JUST COPY THE HOURS
                if (empty($this->hours))
                {
                    $this->hours = $obj->hours;
                }
                // SUBSEQUENT ENTRIES - ADD TO THE HOURS
                else
                {
                    foreach ($obj->hours as $ha => $secs)
                    {
                        // IF THERE IS ALREADY DATA FOR THIS HOUR
                        if (array_key_exists($ha, $this->hours))
                        {
                            // ADD TO THE SECONDS IN THIS HOUR
                            $this->hours[$ha] += $secs;
                        }
                        // IF THERE IS NOT ALREADY DATA FOR THIS HOUR
                        else
                        {
                            // JUST STORE THE SECONDS FOR THIS HOUR
                            $this->hours[$ha] = $secs;
                        }
                    }
                }
            }
        } // END ARRAY COMBINATION AND SUMMATION

        // ELIMINATE THE HOUR IF LESS THAN 30 MINUTES WORKED
        foreach ($this->hours as $ha => $secs)
        {
            if ($secs < 1800) unset($this->hours[$ha]);
        }
        return $this->hours;
    }
}

Open in new window

Hope that helps, ~Ray
0
 
LVL 2

Accepted Solution

by:
c_kedar earned 500 total points
ID: 39861598
This can be done with SQL-only solution:

select login.*, 
(truncToHour(loginDt)+interval i.n hour) as DATE_HOUR,
timestampdiff(MINUTE, greatest(loginDt, truncToHour(loginDt)+interval i.n hour), least(logoutDt, truncToHour(loginDt)+interval i.n+1 hour)) ACTIVE_MINUTES
from login
join intseq i 
	on i.n between 0 and timestampdiff(HOUR, truncToHour(loginDt), truncToHour(logoutDt))
order by user_id, date_hour

Open in new window

truncToHour function should return datetime truncated to hour.
intseq is a table with integer numbers
Required criterion of 30+ minutes can now be implemented on this result set.

Following will explain how this works:
HourOfLogin = truncToHour(loginDt)
HourOfLogut = truncToHour(logoutDt)
NumberOfHoursOfDayInSession = (loginEndHour-HourOfLogut)+1
NthSplitHourStartTime = HourOfLogin+n
NthSplitHourEndTime = HourOfLogin+n+1
activeMinutesInNthSplitHour = least(nThSplitHourEndTime, logoutDt) - greatest(nThSplitHourStartTime,loginDt)

Open in new window


http://sqlfiddle.com/#!2/04e289/7
0
 

Author Closing Comment

by:Dalexan
ID: 39893096
Holy cow, such a beautiful solution!!!!!
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39893163
@Dalexan: If you did not want a PHP solution, why did you waste time asking for one?  More than two weeks ago I posted a tested-and-working PHP solution.  It's traditional at EE to award at least some of the points to the first correct and working solution.  It's not a big deal, just a little surprised that you'd ask for something then ignore it. ~Ray
0
 

Author Comment

by:Dalexan
ID: 39893384
Ray, I value your answer and your help as you have helped me out numerous times on other issues. In hindsight if I could go back and re-award 100 pts to you, your solution would have worked but in the end the business decision was set on a pure SQL solution.
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Why is enum singleton a better approach than static factory 3 66
remote mysql 8 53
Duplicated data in GROUP_CONCAT 2 51
MySqli Real Escape String and SQL Injection 1 83
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question