Get event duration for a specific time period per user

Question

This is an example of my table. I have multiple entries on multiple rows for a given post_id (this is metadata for posts).

post_id | meta_key       | meta_value
________ ________________ ____________________
        |                |
1       | _theDate       | 2016-03-31 12:03:59
1       | _email         | the@email.com
1       | _EventDuration | 32400
2       | _theDate       | 2016-01-06 14:50:22
2       | _email         | the@email.com
2       | _EventDuration | 32400
3       | _theDate       | 2017-02-14 15:32:52
3       | _email         | other@user.net
3       | _EventDuration | 32400
4       | _theDate       | 2016-10-01 22:45:55
4       | _email         | the@email.com
4       | _EventDuration | 32400
5       | _theDate       | 2016-09-25 11:01:39
5       | _email         | other@user.net
5       | _EventDuration | 32400
6       | _theDate       | 2015-11-19 19:08:45
6       | _email         | other@user.net
6       | _EventDuration | 32400

What I am trying to accomplish:

I would like to get these results from the database to make a table displaying the number of hours (_EventDuration) worked for each user (_email) each month (using _theDate for example, 2015-11) for each event (post_id). I can do this using PHP variables on my end.

So I can then make HTML tables to show this information in the following way (I'm not looking for an answer for this part like converting seconds to hours, it's purely just so you know what I want to accomplish with all the data from the table):

Hours the@email.com worked in 2015-11
Date            Hours
2015-11-01      9
2015-11-13      7
2015-11-27      5
2015-11-30      8

Hours the@email.com worked in 2015-12
Date            Hours
2015-12-01      10
2015-12-13      7
2015-12-27      3
2015-12-30      6

All I have for the moment is this request to count the number of events a user has done in 2017 for example :

SELECT COUNT(*) 
  FROM 
     ( SELECT post_id 
         FROM metatable 
        WHERE (meta_key = '_email' AND meta_value LIKE '%$user_email%')                 
           OR (meta_key = '_theDate' AND LEFT(meta_value, 4) LIKE '%2017%') 
        GROUP 
           BY post_id 
       HAVING COUNT(DISTINCT meta_key) = 2
     ) p

Yes, I have this request to count the number of events a user has done in 2017 for example : SELECT COUNT(*) FROM (SELECT post_id FROM `metatable` WHERE (meta_key = '_email' AND meta_value LIKE '%$user_email%') OR (meta_key = '_theDate' AND LEFT(meta_value, 4) LIKE '%2017%') GROUP BY post_id HAVING COUNT(DISTINCT meta_key) = 2) p — xtojump, May 25 '17 at 16:14
Can you edit your post and write down this query and one thing please, why there is this p in the last of the query ? — Omis Brown, May 25 '17 at 16:18
@Alex78191 This is the way to normalize a general metadata table so you can have arbitrary keys. — Barmar, May 25 '17 at 16:23
It's common in frameworks like WordPress, because they can't predict what kind of attributes you'll need. — Barmar, May 25 '17 at 16:24
OK, maybe normalization is the wrong name for this. The point is that you don't want to hard-code all the possible keys into column names when you're designing a general-purpose data store. — Barmar, May 25 '17 at 16:25
I want to get per user : the dates of each event and the `_EventDuration` value (which I can convert to hours later). — xtojump, May 25 '17 at 16:31
If it was me, and I was obliged to use an EAV model, I'd separate the attributes out into discrete tables based on data type. Just sayin' — Strawberry, May 25 '17 at 16:49

score 2 · Accepted Answer · edited May 25 '17 at 16:50

2

You can use a subquery to pivot the table to get email, date, and Hours into different columns for each post. Then you can use a normal grouped query to get the total per day and user.

SELECT Email, Date, SUM(Hours) AS Hours
FROM (
    SELECT MAX(IF(meta_key = '_email', meta_value, NULL)) AS Email,
           MAX(IF(meta_key = '_theDate', DATE(meta_value), NULL)) AS Date, 
           SUM(IF(meta_key = '_EventDuration', meta_value, 0))/3600 AS Hours
    FROM metatable
    GROUP BY post_id
) AS x
GROUP BY Email, Date
ORDER BY Email, Date

edited May 25 '17 at 16:50

Strawberry

33,750
13
40
57

answered May 25 '17 at 16:21

Barmar

741,623
53
500
612

Sorry, someone renamed my question and that's not what I want to find. I want to get per user : the dates of each event and the `_EventDuration` value (which I can convert to hours later). – xtojump May 25 '17 at 16:30
This looks like a great start. So then I can get the results using PHP and search the array for the user's email address I guess and find the related info? Please correct me if I'm wrong. I'm kind of new to this! PS : This is on Wordpress – xtojump May 25 '17 at 17:00
You can use `IF(meta_key = '_email' AND meta_value = 'the@email.com`, meta_value, NULL))` to match a specific email instead of returning them all. – Barmar May 25 '17 at 17:05
It's just a way to ignore all the `NULL` values from the rows that don't match the `IF` criteria. See https://stackoverflow.com/questions/7674786/mysql-pivot-table – Barmar May 26 '17 at 00:02

Get event duration for a specific time period per user

1 Answers1