javascript - How to find closest datetimes to 6-hour intervals?
Get the solution ↓↓↓I have table of several million records containing indicating how many "things" (int
) existed at random times (datetime
) throughout the day. (One day could have 100's or 1000's of records.)
I need to report how many "things" existed at roughly[00:00, 06:00, 12:00, 18:00]
daily ... meaning there may or may not be records for those times, so if there isn't I'd need the closest record (before or after).
I tried a few methods but the runtimes aren't feasible for production. There's got to be a more efficient method that I'm not thinking of. Here's one of my attempts but I also tried using aUNION
query, as well as running 4 separate queries from PHP. (The solution could be in any of MySQL, PHP, or JS/JQuery).
SELECT dt, SUM(things) thgs
FROM myTable
WHERE dt IN (SELECT MIN(dt) FROM myTable GROUP BY DATE(dt))
OR dt IN (SELECT DATE_SUB(MIN(DATE_ADD(dt, INTERVAL 6 HOUR)), INTERVAL 6 HOUR) FROM myTable
GROUP BY DATE_SUB(DATE(DATE_ADD(dt, INTERVAL 6 HOUR)), INTERVAL 6 HOUR))
OR dt IN (SELECT DATE_SUB(MIN(DATE_ADD(dt, INTERVAL 12 HOUR)), INTERVAL 12 HOUR) FROM myTable
GROUP BY DATE_SUB(DATE(DATE_ADD(dt, INTERVAL 12 HOUR)), INTERVAL 12 HOUR))
OR dt IN (SELECT DATE_SUB(MIN(DATE_ADD(dt, INTERVAL 18 HOUR)), INTERVAL 18 HOUR) FROM myTable
GROUP BY DATE_SUB(DATE(DATE_ADD(dt, INTERVAL 18 HOUR)), INTERVAL 18 HOUR))
GROUP BY dt
ORDER BY dt
Thoughts?
Answer
Solution:
Very late to this question, but this is generally how I solve a similar sort of thing when presenting operational statistics to managers curious about how many students are using our "learning portals" at any given time:
SELECT tmp.`id`, tmp.`start_at`, tmp.`until_at`, COUNT(DISTINCT ss.`id`) as `things`
FROM `Status` ss INNER JOIN (SELECT (h*1000+t*100+u*10+v+1) as `id`,
DATE_SUB(DATE_FORMAT(DATE_ADD(Now(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'), INTERVAL ((h*1000+t*100+u*10+v+1) - 1) * 6 HOUR) as `start_at`,
DATE_SUB(DATE_SUB(DATE_FORMAT(DATE_ADD(Now(), INTERVAL 1 DAY), '%Y-%m-%d 06:00:00'), INTERVAL ((h*1000+t*100+u*10+v+1) - 1) * 6 HOUR), INTERVAL 1 SECOND) as `until_at`
FROM (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c,
(SELECT 0 v UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
WHERE (h*1000+t*100+u*10+v+1) > 0) tmp ON ss.`event_at` BETWEEN tmp.`start_at` AND tmp.`until_at`
WHERE ss.`event_at` >= DATE_FORMAT(DATE_SUB(Now(), INTERVAL 2 WEEK), '%Y-%m-%d 00:00:00')
GROUP BY tmp.`id`, tmp.`start_at`, tmp.`until_at`
ORDER BY tmp.`id`;
How this works:
- the most nested subquery generates a sequence of numbers from 1 ~ 10,000
- this sequence is used to work out time periods of — for this example — six hours
- there are roughly 10,000 six-hour periods in 7 years
- group the "things" by
start_at
anduntil_at
- filter on a reasonable timeframe
I tested this on my development workstation, which has a 4th Gen Core i7 and 32GB RAM. The query took just under 30 seconds to return data on a statistics table with 27-million+ records.
Here is a sample ... not that it will mean anything beyond showing the above SQL is cromulent:
Note: The development box does not contain any data for the current month, hence the gap.
In the event that you do not want to show periods where there are zero "things", then that can be filtered out afterwards.
If you (or anyone else) is still looking for a solution to this sort of problem, this might offer something to think about.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: filter_sanitize_string deprecated
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.