grouping query results by time intervals aggregate

Question

I'm working on a MySQL database system that will allow my company to process our journey time survey data in a fraction of the time it currently takes. I have got the following MySQL command that does the calculation:

select anpr_1_unique.date as "Date",
anpr_1_unique.NETBIOSNAME as "ID for site A",
anpr_1_unique.time as "Timestamp at site A",
anpr_3_unique.NETBIOSNAME as "ID for site B",
anpr_3_unique.time as "Timestamp at site B",
anpr_1_unique.plate as "Plate",
if (timediff(anpr_3_unique.time,anpr_1_unique.time) like "%-%", null,timediff(anpr_3_unique.time,anpr_1_unique.time)) as "Journey time in direction 1",
if (timediff(anpr_1_unique.time,anpr_3_unique.time) like "%-%", null,timediff(anpr_1_unique.time,anpr_3_unique.time)) as "Journey time in direction 1",
if (timediff(anpr_1_unique.time,anpr_3_unique.time) like "%-%", "A->B","B->A") as "Direction of travel"
from anpr_1_unique inner join anpr_3_unique on anpr_1_unique.plate=anpr_3_unique.plate 
where anpr_1_unique.date = "2013-02-26" and anpr_3_unique.date="2013-02-26" and anpr_1_unique.time like "%06:%%:%%%" order by anpr_1_unique.time

which produces a table in the following format:

+------------+---------------+---------------------+---------------+---------------------+---------+-----------------------------+-----------------------------+---------------------+
| Date       | ID for site A | Timestamp at site A | ID for site B | Timestamp at site B | Plate   | Journey time in direction 1 | Journey time in direction 1 | Direction of travel |
+------------+---------------+---------------------+---------------+---------------------+---------+-----------------------------+-----------------------------+---------------------+
| 2013-02-26 | Farnham N 1   | 06:50:52            | Farnham S     | 06:54:32            | LS60JAO | 00:03:40                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:51:33            | Farnham S     | 06:53:32            | FH53WGW | 00:01:59                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:51:51            | Farnham S     | 06:54:23            | V987USD | 00:02:32                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:52:17            | Farnham S     | 06:54:28            | BK61RNY | 00:02:11                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:52:30            | Farnham S     | 06:54:35            | WU59SXP | 00:02:05                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:53:02            | Farnham S     | 12:23:33            | NA08UKV | 05:30:31                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:53:44            | Farnham S     | 06:56:09            | KC04CTF | 00:02:25                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:53:46            | Farnham S     | 06:56:11            | LL58YAJ | 00:02:25                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:54:41            | Farnham S     | 06:57:44            | AP57CWE | 00:03:03                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:54:46            | Farnham S     | 14:55:29            | EU55LRF | 08:00:43                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:55:13            | Farnham S     | 06:58:08            | AJ60KVK | 00:02:55                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:55:19            | Farnham S     | 06:58:44            | T96ALO  | 00:03:25                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:55:57            | Farnham S     | 06:58:46            | F604WNV | 00:02:49                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:56:22            | Farnham S     | 06:58:50            | S905AAP | 00:02:28                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:56:52            | Farnham S     | 06:59:35            | LO08SKV | 00:02:43                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:56:55            | Farnham S     | 06:59:37            | KT53TNK | 00:02:42                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:57:45            | Farnham S     | 07:00:33            | LC60EOR | 00:02:48                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:58:44            | Farnham S     | 07:06:15            | S278VNA | 00:07:31                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:58:49            | Farnham S     | 07:00:46            | X906GVT | 00:01:57                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:59:15            | Farnham S     | 07:01:35            | YS11AWP | 00:02:20                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:59:44            | Farnham S     | 07:01:46            | LP12NWA | 00:02:02                    | NULL                        | A->B                |
| 2013-02-26 | Farnham N 1   | 06:59:59            | Farnham S     | 07:02:29            | RJ11BUA | 00:02:30                    | NULL                        | A->B                |
+------------+---------------+---------------------+---------------+---------------------+---------+-----------------------------+-----------------------------+---------------------+
22 rows in set (0.09 sec)

This is just a 10-minute sample of the results. I am happy with this, and how it has turned out, however I want to be able to group these results into aggregation periods of 5,10 or 15 minutes

I have looked around for other potential solutions on this site and others, most notably advising the use of GROUP BY UNIX_TIMESTAMP(<time_stamp>) DIV <time> to produce the aggregation. I have tried this, and it didn't work with this query.

What can I do to get this data aggregated efficiently? can it be done with the query i have produced, or does this need to be rewritten?

Update

I managed to get the calculation to do roughly what i wanted with the following SQL code:

select 
anpr_1_unique.date as "Date", 
str_to_date(concat(date_format(anpr_1_unique.time, '%H'),':',(floor(date_format(anpr_1_unique.time, '%i')/30)*30), ':00'), '%H:%i:%s') as starttime,
anpr_1_unique.NETBIOSNAME as "ID for site A",
anpr_3_unique.NETBIOSNAME as "ID for site B",
if (avg(timediff(anpr_3_unique.time,anpr_1_unique.time) like "%-%"), null,time(avg(timediff(anpr_3_unique.time,anpr_1_unique.time)))) as "Journey time in direction  A->B"
from anpr_1_unique 
inner join anpr_3_unique on anpr_1_unique.plate=anpr_3_unique.plate 
where anpr_1_unique.date = "2013-02-26" and anpr_3_unique.date="2013-02-26" 
and timediff(anpr_3_unique.time,anpr_1_unique.time) between "00:00:00" and "00:30:00"
GROUP BY   starttime; 
show warnings;

which produces data like this:

+------------+-----------+---------------+---------------+---------------------------------+
| Date       | starttime | ID for site A | ID for site B | Journey time in direction  A->B |
+------------+-----------+---------------+---------------+---------------------------------+
| 2013-02-26 | 06:30:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 07:00:00  | Farnham N 1   | Farnham S     | 00:03:46.3276  |
| 2013-02-26 | 07:30:00  | Farnham N 1   | Farnham S     | 00:04:51.5588  |
| 2013-02-26 | 08:00:00  | Farnham N 1   | Farnham S     | 00:11:10.8462  |
| 2013-02-26 | 08:30:00  | Farnham N 1   | Farnham S     | 00:11:36.6410  |
| 2013-02-26 | 09:00:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 09:30:00  | Farnham N 1   | Farnham S     | 00:05:39.0714  |
| 2013-02-26 | 10:00:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 10:30:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 11:00:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 11:30:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 12:00:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 12:30:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 13:00:00  | Farnham N 1   | Farnham S     | 00:05:17.1250  |
| 2013-02-26 | 13:30:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 14:00:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 14:30:00  | Farnham N 1   | Farnham S     | 00:05:06.8864  |
| 2013-02-26 | 15:00:00  | Farnham N 1   | Farnham S     | 00:09:07.2308  |
| 2013-02-26 | 15:30:00  | Farnham N 1   | Farnham S     | 00:13:02.2558  |
| 2013-02-26 | 16:00:00  | Farnham N 1   | Farnham S     | NULL           |
| 2013-02-26 | 16:30:00  | Farnham N 1   | Farnham S     | 00:26:45.7143  |
| 2013-02-26 | 17:30:00  | Farnham N 1   | Farnham S     | 00:28:04.0000  |
| 2013-02-26 | 18:00:00  | Farnham N 1   | Farnham S     | 00:22:56.0667  |
| 2013-02-26 | 18:30:00  | Farnham N 1   | Farnham S     | NULL           |
+------------+-----------+---------------+---------------+---------------------------------+

however, as shown, this generates a number of NULL results where there should be data. the warnings show an equal number of " | Warning | 1292 | Truncated incorrect time value: '(number)' | " messages. I had a similar problem when working out the calculation, which i solved by using the timediff() command instead of manually calculating. I'm not sure what i can do with this now to "de-null" these results.

any suggestions on what i could do here, now?

AdrianBR · Answer 1 · 2013-10-10T16:40:40.583

1

you need to round your division result. Do this with floor, ceil or round

 GROUP BY    floor(UNIX_TIMESTAMP(<time_stamp>)/(seconds*minutes))

GROUP BY    floor( UNIX_TIMESTAMP(<time_stamp>)/(60*15) ) - group into 15 minute intervals

specifically;

 GROUP BY    floor( UNIX_TIMESTAMP(CONCAT(anpr_1_unique.date, ' ', npr_1_unique.time))/(60*15) )

edited Oct 10 '13 at 16:40

answered Oct 10 '13 at 15:58

AdrianBR

2,762
1
15
29

apologies if I'm being obtuse. what should i use at the in that command? i've tried putting the `group by` command at the end of the query above, with the anpr_1_unique.time, as well as the actual calculation ( `timediff(anpr_3_unique.time,anpr_1_unique.time) ` ). neither of these have worked – almg Oct 10 '13 at 16:22
CONCAT(anpr_1_unique.date, ' ', anpr_1_unique.time) to get timestamp – AdrianBR Oct 10 '13 at 16:39

grouping query results by time intervals aggregate

Update

1 Answers1