Peak visit counts by datetime period
I have a visits table (id int, start datetime, end datetime), and I you wish to track peak visit counts.
Example data:
+------+---------------------+---------------------+
| id | date-time-1 | date-time-2 |
+------+---------------------+---------------------+
| 1059 | 2016-07-04 19:13:00 | 2016-07-04 19:20:05 |
| 1060 | 2016-07-04 19:13:30 | 2016-07-04 19:14:25 |
| 1061 | 2016-07-04 19:14:39 | 2016-07-04 19:20:05 |
| 1062 | 2016-07-05 02:34:40 | 2016-07-05 02:45:23 |
| 1063 | 2016-07-05 02:34:49 | 2016-07-05 02:45:34 |
+------+---------------------+---------------------+
The expected result should be:
+-------------------------------------------+-------+
| date-time-1 | date-time-2 | count |
+-------------------------------------------+-------+
| 2016-07-04 19:13:00 | 2016-07-04 19:13:29 | 1 |
| 2016-07-04 19:13:30 | 2016-07-04 19:14:25 | 2 |
| 2016-07-04 19:14:26 | 2016-07-04 19:14:38 | 1 |
| 2016-07-04 19:14:39 | 2016-07-04 19:20:05 | 2 |
| 2016-07-04 19:20:06 | 2016-07-05 02:34:39 | 0 |
| 2016-07-05 02:34:40 | 2016-07-05 02:34:48 | 1 |
| 2016-07-05 02:34:49 | 2016-07-05 02:45:23 | 2 |
| 2016-07-05 02:45:24 | 2016-07-05 02:45:34 | 1 |
+------+------------------------------------+-------+
I've already asked this question on Stack Overflow (http://stackoverflow.com/questions/38193454/mysql-peak-visit-counts-by-datetime-period/) but the answers (queries) don't provide the correct results.
I'm posting my question also here with the hope that some MySQL expert can help with this query that seems difficult / challenging.
Best regards and thanks!
Subject
Written By
Posted
Peak visit counts by datetime period
July 05, 2016 08:04PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.