Advertisement
Guest User

MySQL - Peak visit counts by datetime period

a guest
Jul 5th, 2016
74
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.59 KB | None | 0 0
  1. Example data:
  2.  
  3. +------+---------------------+---------------------+
  4. | id | date-time-1 | date-time-2 |
  5. +------+---------------------+---------------------+
  6. | 1059 | 2016-07-04 19:13:00 | 2016-07-04 19:20:05 |
  7. | 1060 | 2016-07-04 19:13:30 | 2016-07-04 19:14:25 |
  8. | 1061 | 2016-07-04 19:14:39 | 2016-07-04 19:20:05 |
  9. | 1062 | 2016-07-05 02:34:40 | 2016-07-05 02:45:23 |
  10. | 1063 | 2016-07-05 02:34:49 | 2016-07-05 02:45:34 |
  11. +------+---------------------+---------------------+
  12.  
  13. Expected result:
  14.  
  15. +-------------------------------------------+-------+
  16. | date-time-1 | date-time-2 | count |
  17. +-------------------------------------------+-------+
  18. | 2016-07-04 19:13:00 | 2016-07-04 19:13:29 | 1 |
  19. | 2016-07-04 19:13:30 | 2016-07-04 19:14:25 | 2 |
  20. | 2016-07-04 19:14:26 | 2016-07-04 19:14:38 | 1 |
  21. | 2016-07-04 19:14:39 | 2016-07-04 19:20:05 | 2 |
  22. | 2016-07-04 19:20:06 | 2016-07-05 02:34:39 | 0 |
  23. | 2016-07-05 02:34:40 | 2016-07-05 02:34:48 | 1 |
  24. | 2016-07-05 02:34:49 | 2016-07-05 02:45:23 | 2 |
  25. | 2016-07-05 02:45:24 | 2016-07-05 02:45:34 | 1 |
  26. +------+------------------------------------+-------+
  27.  
  28. Your query result:
  29.  
  30. dtstart dtend count(*)
  31. July, 04 2016 19:13:00 2016-07-04 19:13:29 1
  32. July, 04 2016 19:13:30 2016-07-04 19:14:24 2
  33. July, 04 2016 19:14:25 2016-07-04 19:14:38 1
  34. July, 04 2016 19:14:39 2016-07-04 19:20:04 2
  35. July, 04 2016 19:20:05 2016-07-05 02:34:39 1
  36. July, 05 2016 02:34:40 2016-07-05 02:34:48 1
  37. July, 05 2016 02:34:49 2016-07-05 02:45:22 2
  38. July, 05 2016 02:45:23 2016-07-05 02:45:34 1
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement