title: mysql get sum of hours / minutes / seconds
body: <p>I have a table with: <strong>userid</strong> and <strong>timestamp</strong> each time a user opens a page a new field is inserted.</p>
  
  <p>I am trying to get the total amount of <strong>hours</strong> / <strong>minutes</strong> / <strong>days</strong> / <strong>weeks</strong> that appear in a 1 month interval for multiple users.</p>
  
  <p>I have tried a bunch of different queries but each have ended up terribly inefficient.</p>
  
  <p>Ideally I'd like to end up with something like:</p>
  
  <pre><code>   userid | minutes | hours | days | weeks
        1      10080     168      7      1
        2       1440      24      1      0
  </code></pre>
  
  <p>Hopefully someone can shed some light on how to do this.        </p>
  
  <p>Below is a query that I tried:</p>
  
  <pre><code>  SELECT 
      w.time AS `week`, 
      d.time AS `day`, 
      h.time AS `hour`, 
      m.time AS `minutes`
      FROM (
               SELECT 
              SUM( t.time ) AS `time`
               FROM (
                      SELECT 
                      COUNT( DISTINCT WEEK( `timestamp` ) ) AS `time`
                  FROM table
                      WHERE 
                      userid =  "1"
                      AND 
                      `timestamp` &gt; DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
                      GROUP BY MONTH( `timestamp` )
                      ) t
           ) w, 
          (
               SELECT 
              SUM( t.time ) AS `time`
               FROM (
                      SELECT 
                      COUNT( DISTINCT DAY( `timestamp` ) ) AS `time`
                      FROM table
                      WHERE 
                      userid =  "52"
                      AND 
                      `timestamp` &gt; DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
                      GROUP BY MONTH( `timestamp` )
               ) t
              ) d,
           (
          SELECT 
              SUM( t.timestamp ) AS `time`
              FROM (
                  SELECT 
                      COUNT( DISTINCT HOUR( `timestamp` ) ) AS `time`
                      FROM table
                      WHERE 
                      userid =  "1"
                      AND 
                      `timestamp` &gt; DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
                  GROUP BY DAY( `timestamp` )
                   ) t
              ) h, 
          (
              SELECT 
              SUM( t.timestamp ) AS `time`
              FROM (
                  SELECT 
                      COUNT( DISTINCT MINUTE( `timestamp` ) ) AS `time`
                   FROM table
                  WHERE 
                      userid =  "1"
                  AND 
                      `timestamp` &gt; DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
                      GROUP BY HOUR( `timestamp` )
               ) t
           ) m
  </code></pre>
  
  <p>It seems awfully excessive for this task, maybe someone has something better?</p>