MySQL: Get table with 30minute intervals from another with dates

Question:

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.

Answer:

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
Scroll to Top