I have two tables. First one is called posts, and a second one postmeta. (If someone notices, I'm working with a Wordpress db which is not important to know for this task).
posts table looks like this (for this purposes shortened).
ID | post_title | post_status | post_type
------------------------------------------
1 | One | publish | hours
2 | Two | publish | hours
postmeta table looks like this. Date format is d.m.Y. G:i:s.
meta_id | post_id | meta_key | meta_value
------------------------------------------
1 | 1 | from | 1.1.2017. 10:00:00
2 | 1 | to | 1.1.2017. 16:00:00
3 | 2 | from | 2.1.2017. 12:00:00
4 | 2 | to | 2.1.2017. 15:00:00
In those tables ID = post_id.
The wanted result is a table below where date_diff is a difference between from and to in hours which has to be calculated by SQL (date_diff = to - from). Note that meta_key is defined as VARCHAR and meta_value as LONGTEXT which makes calculation harder.
ID | title | from | to | date_diff
------------------------------------------------------------------
1 | 1 | 1.1.2017. 10:00:00 | 1.1.2017. 16:00:00 | 6
2 | 1 | 2.1.2017. 12:00:00 | 2.1.2017. 15:00:00 | 3
This is the code I have for now. Making rows become columns is a bit problematic for me, and a calculation even more.
SELECT posts.ID, posts.post_title, postmeta.meta_key, postmeta.meta_value
FROM posts
INNER JOIN postmeta
ON posts.ID = postmeta.post_id
WHERE post_status = 'publish'
AND post_type = 'hours'
AND (postmeta.meta_key = 'from' OR postmeta.meta_key = 'to');
Thanks alot. :)