MYSQL: average of varchar y:m:d in a group

Question

I have a mysql column named time_on_site.
The column is stored as a variable h:m:s

Currently in my code I have the following:

foreach($db->query("SELECT country,siteID,referring_site,COUNT(country)
  AS thecount,COUNT(distinct country)
  FROM rotator_tracking WHERE siteID='$_GET[site]'
  GROUP BY country, referring_site ORDER BY thecount ASC") as $row) {  

    if (isset($row['referring_site'])) {  
       echo '
         <tr>
           <td class="country" style="padding: initial;">
           ' . $row['referring_site'] . '
           </td>';  
    } else { 
       echo '
         <tr>
           <td class="country" style="padding: initial;">
           Unknown
           </td>'; 
    } 

       echo '
           <td class="country_referral" style="padding: initial;">
           ' . $row['country'] . '
           </td>';       

       echo '
           <td class="country_referral" style="padding: initial;">
           ' . $row['thecount'] . '
           </td>';       

       echo '
           <td class="country_referral" style="padding: initial;">' . 
           $row['COUNT(distinct country)'] . '
           </td>
         </tr>';  
}

What I am trying to figure out how to do is to add another td that shows an average time on site based on both the country and referring site grouping using the value from time_on_site.

So effectively the table should loop:

referring site : country : total visits : unique visits : average time on site

Because of the date being a variable format, it seems its not as easy to just SUM and divide by total. Help? How should I do this?

I don't know how I would do arithmetic like that from within the SQL query given that `time_on_site` is stored as a string. I would probably to the fetch all results into an array in PHP, convert the strings to numbers, do the arithmetic and display the result in the format I wish to use. — BeetleJuice, Jul 17 '16 at 02:52
Just a general note that you should NEVER trust user input like you're doing. It's called [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Machavity, Jul 17 '16 at 03:08
I have NO idea what INPUT you are referring to... Is it the $_GET['id']?? How else would one determine to which site a user wants their tracking info from? Other than that, there is no user data being collected except via visits to url's. And granted most things passed via a browser can be spoofed, but thats no reason to stop tracking as the average user isn't spoofing details.... Seriously I have no idea what user input you are referring to. — Bruce, Jul 17 '16 at 03:13
@Bruce Yes. You should never pass a raw `$_GET` or `$_POST` into your SQL — Machavity, Jul 17 '16 at 04:07
I have figured out I can do a `STR_TO_DATE( time_on_site, '%T' ) AS average` but I still can't figure out how to get the average. If I try `AVG('STR_TO_DATE( time_on_site, '%T' )) AS average` it spits out things like 0.0602 instead of an average in hours:minutes:seconds. — Bruce, Jul 17 '16 at 04:10
@Bruce by the way, @Machavity's advice is good. Your code leaves you very vulnerable to anyone who cares to take over your DB by manipulating the `site` input. It's not the average user he was worried about. He wasn't saying that you shouldn't use that data at all, only that you should either clean it first (in this case, probably as easy as `$id=(int)$_GET['site']` or use prepared statements to run your queries. Thanks for the tip about `STR_TO_DATE`. Sounds handy. — BeetleJuice, Jul 17 '16 at 04:25
@Machavity I believe my answer solves the injection problem. If I am not understanding and there is more to it please do let me know. — Bruce, Jul 17 '16 at 08:29
@Bruce it looks like you're using prepared statements. Without the rest of the code I can't be sure, but it looks like you're on the right track — Machavity, Jul 17 '16 at 18:23

score 0 · Accepted Answer · answered Jul 17 '16 at 08:27

So I spent a good amount of time trying to solve this problem. In the end, the solution was rather easy.

As the format was already hh:mm:ss there was no need to keep it as varchar, so I simply changed the type from varchar to time.

after that it was pretty much standard.

SELECT SEC_TO_TIME( AVG( TIME_TO_SEC( `time_on_site` ) ) ) 
 AS timeSum, country,siteID,referring_site,COUNT(country) 
 AS thecount,COUNT(distinct country) 
FROM rotator_tracking 
WHERE siteID=':site' 
GROUP BY country, referring_site 
ORDER BY thecount ASC

MYSQL: average of varchar y:m:d in a group

1 Answers1