0

I'm trying to add a feature in my application that totals how much time a user spends in the system by the week, month, etc. 

$select = "SELECT TOTAL_HRS FROM timeclock WHERE USERNAME = '$sessuser' AND CLOCK_OUT BETWEEN '$dbpast' AND '$dbnow'";

I have a MySql result of two sample time entries: 00:00hr:04min:08s and 00:00hr:12min:52s. Users can have more.

TOTAL_HRS is a varchar column, so when I put a sum() on it, it returns a 0.

Here's what I have so far: 

while($row = $query->fetch_assoc()){

  print_r($row);
$sanitized = preg_replace("/[^0-9:]+/", "", $row);
  print_r($sanitized);
$joinarr = implode(':', $sanitized);
$parts = explode(':', $joinarr);
  print_r($parts);

$zerodate = new DateTime('0000-01-01 00:00:00');
$addhrs += $parts[1];
$addmin += $parts[2];

print_r($addhrs);

$interval = $zerodate->add(new DateInterval('P' .$parts[0].'DT'.$parts[1].'H'.$parts[2].'M'.$parts[3].'S'));

$totalhrs = $interval->format('%D:%Hhr:%Imin:%Ss');
print_r($totalhrs);

}

I get funky junk in return: 0%Sat:%0012Sat, 01 Jan 0000 00:12:52 +0100:%001121:%st52

What I need to return back is: something like: 00:00hr:17min:00s. I don't plan on storing this, just want it to display on a page.

I need some help figuring this out. I'm sure there is a better way. I'm not that great at functions. Or should I send the results through jquery and handle them there? Which side is more efficient at handling DateTime? The help would be much appreciated. Thanks!

hnewbie
  • 151
  • 1
  • 15
  • How are you storing the amount of time the users spends? It would probably be easier to store it in seconds, or as a timestamp with start and end times. – chris85 Nov 12 '17 at 21:43
  • ...I didn't think of that. lol. I'm storing the start and end times in DateTime fields on my database, then totaling up "session times",converting them storing them as strings. I wanted those to display as strings. What's the difference between DateTime and Timestamp? – hnewbie Nov 12 '17 at 21:51
  • 1
    Timestamp is the number of seconds that have passed since 1970 (currently 1510524424). A date time is a date and the time. There are functions to subtract datetime's though. See https://stackoverflow.com/questions/10907750/calculate-difference-between-two-datetimes-in-mysql. `varchar` and math will be a huge pain, don't go that route. – chris85 Nov 12 '17 at 22:08

2 Answers2

0

I would do it like this:

Create a column in the database to save each session time on site. I would probably set it as an integer rather than a timestamp column. I just need it to return a number. I'd have a separate timestamp column to record the time of the visit.

Client side bind a javascript/jquery function to window.onload which saves to localstorage / sets a cookie to current timestamp when the page is loaded, use moment.js library to display total site time to user and have it count up in real time. Check to see if cookies already exist and if so process them before setting new values.

Either bind another function to window.onunload to save another cookie with the timestamp when the user navigates away from the site, or use setInterval to save the latest timestamp every minute or so while they are on the site.

On page load check if cookies/localstorage have values then subtract timestamp of page load from timestamp of page unload to get number of seconds they spent on site. Save that to database and use the timestamp of the unload event to set the timestamp of their visit.

Run relevant queries on the database to return total seconds on site in last day/week/month whatever and pass them to moment.js via ajax. It can displaying a counter like you describe from a string containing a value in seconds using about 2 lines of code.

miknik
  • 5,748
  • 1
  • 10
  • 26
  • 0_0 this is slick. I like. I don't know much about cookies, but will have to start to incorporate them going forward. – hnewbie Nov 13 '17 at 01:09
0

You can calculate the number of seconds between two datetime values using TIMESTAMPDIFF() and then sum that. e.g.

SELECT SUM(TIMESTAMPDIFF(SECOND,CLOCK_IN,CLOCK_OUT)) AS TOTAL_SECS
FROM timeclock 
WHERE USERNAME = '$sessuser' AND CLOCK_OUT BETWEEN '$dbpast' AND '$dbnow'

see: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff

You might currently be using TIMEDIFF() which gives you those "funky" values

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • I actually didn't use timediff() in the query, I was trying to hack resulting strings, which unfortunately made the mistake of travelling down that rabbit hole. As a beginner, everything that I do is a learning experience. Your answer saved a lot of space on my php file. Thank you! I was able to turn the seconds into a time format using this answer: https://stackoverflow.com/questions/3856293/how-to-convert-seconds-to-time-format – hnewbie Nov 13 '17 at 12:06
  • avoid string manipulation if there is ever any chance of using numbers instead :) it is a **digit**al world. and note, dates and times are numeric - just a wee bit more complex than 0 to 9 – Paul Maxwell Nov 13 '17 at 12:28