I have a table consisting of three date fields in YYYY-MM-DD HH-MM-SS format: 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. There is no possibility that all three are NULL
; for instance:
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 to a table that for daily half hour intervals 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, exits and approaches for each day in half-hour intervals; It is possible that there are records in any segment, be it [00:00 - 00:30], [12:00 - 12:30] or [23:30 - 24:00].
Start of 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 one table with a common timespan field coming from... any of them? Of all? Of none?
There are many more nears than in and out , although there are sometimes 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 field of intervals that 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:
I'm basing it 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-repeating dates (they could be repeated between fields) while calculating the start and end date and time of the range.Finally, I review all the records of the table
ap_summary_temp
comparing each one with each record of the previous subquery, to add1
(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 the code.
You can test the result online at this link .
The result for your input data would be: