Solved

Hours count if over x minutes of hour

Posted on 2014-02-04
15
354 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
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 16

Expert Comment

by:HooKooDooKu
Comment Utility
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
Comment Utility
Yes, dates change where the user is logged in through midnight...
0
 
LVL 16

Expert Comment

by:HooKooDooKu
Comment Utility
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
 

Author Comment

by:Dalexan
Comment Utility
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
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

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

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
Holy cow, such a beautiful solution!!!!!
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
@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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now