I have a table consisting of three date fields formatted YYYY-MM-DD HH-MM-SS: in , out and near . If there is in, there must also be out , and both may or may not be accompanied by near ; near can exist on its own. It is not possible that all three are
NULL ; for example:
in_time out_time near_time 2019-10-02 10:46:31 2019-10-02 12:34:43 2019-10-02 09:51:57 NULL NULL 2019-10-02 11:51:08 2019-10-02 12:02:40 2019-10-02 12:08:56 NULL
And I need to convert it into a table that for daily intervals of half an hour counts the number of in , out and near ; such that:
interval qt_in qt_out qt_near 2019-10-02 08:30:00 1 0 2 2019-10-02 09:00:00 1 1 5 2019-10-02 09:30:00 2 0 3
That is, I need to know the number of entries, departures and approaches for each day in half-hour sections; It is possible that there are records in any section, be it [00:00 – 00:30], [12:00 – 12:30] or [23:30 – 24:00].
Start of the edition:
Right now I have this:
SELECT from_unixtime((unix_timestamp(in_time) DIV (60 * 30)) * (30 * 60)) AS interval_in, sum(CASE WHEN in_time IS NOT NULL THEN 1 ELSE 0 END) AS qt_in FROM count_config_module.ap_summary GROUP BY interval_in ORDER BY interval_in ;
Analogous queries for out and near return the results I expect for each date field, but now I would need to put it all together in the same table with a common time interval field coming from … any of them? Of all? None?
There are many more nears than in and out , although sometimes there are in and out without near , so it is quite possible that absolutely all the necessary intervals for in and out are already present in near .
With this I get the common range of intervals I want:
SELECT DISTINCT from_unixtime((unix_timestamp(in_time) DIV (60 * 30)) * (30 * 60)) AS interval_all FROM count_config_module.ap_summary UNION SELECT DISTINCT from_unixtime((unix_timestamp(out_time) DIV (60 * 30)) * (30 * 60)) FROM count_config_module.ap_summary UNION SELECT DISTINCT from_unixtime((unix_timestamp(near_time) DIV (60 * 30)) * (30 * 60)) FROM count_config_module.ap_summary ;
However, I see no way to take advantage of it …
End of edition.
Thanks in advance.
The solution could be the following:
-- Buscamos los campos que están entre los rangos de fecha SELECT interval_bajo intervalo, SUM( CASE WHEN in_time BETWEEN interval_bajo AND interval_alto THEN 1 ELSE 0 END ) in_time, SUM( CASE WHEN out_time BETWEEN interval_bajo AND interval_alto THEN 1 ELSE 0 END ) out_time, SUM( CASE WHEN near_time BETWEEN interval_bajo AND interval_alto THEN 1 ELSE 0 END ) near_time FROM ( SELECT DISTINCT FROM_UNIXTIME(interval_all) interval_bajo, FROM_UNIXTIME(interval_all + (60 * 30) - 1) interval_alto FROM ( SELECT DISTINCT (UNIX_TIMESTAMP(in_time) DIV (60 * 30)) * (60 * 30) AS interval_all FROM ap_summary UNION SELECT DISTINCT (UNIX_TIMESTAMP(out_time) DIV (60 * 30)) * (60 * 30) FROM ap_summary UNION SELECT DISTINCT (UNIX_TIMESTAMP(near_time) DIV (60 * 30)) * (60 * 30) FROM ap_summary ) tabla_intermedia WHERE interval_all IS NOT NULL ) ap_summary_temp INNER JOIN ap_summary GROUP BY interval_bajo
I'm based on a subquery like the one you created, but without converting to a date, called
ap_summary_temp . In it then I get the non-repeated dates (they could be repeated between fields) while I calculate the date and time of the beginning of the range and the end.
Finally, I review all the records in the
ap_summary_temp table, comparing each one with each record in the previous subquery, to add
1 (as you did in your example) if each field is within the range.
The query can probably be optimized considerably, but at least it works without overcomplicating your code.
You can test the result online at this link .
The result for your input data would be:
intervalo in_time out_time near_time 2019-10-02 09:30:00 0 0 1 2019-10-02 10:30:00 1 0 0 2019-10-02 11:30:00 0 0 1 2019-10-02 12:00:00 1 1 0 2019-10-02 12:30:00 0 1 0