I have a MySQL query, provided by another user in another thread, that produces a list of a day's ANPR Journey time results in the following format:
Plate Date Starttime Endtime Journey Time
YF10PXE 2014-06-18 10:00:32 10:03:22 00:02:50
KG55GOA 2014-06-18 10:00:39 10:03:25 00:02:46
N380LGN 2014-06-18 10:00:43 10:03:44 00:03:01
X557EFB 2014-06-18 10:01:05 10:03:31 00:02:26
AJ61AOS 2014-06-18 10:01:32 10:41:09 00:39:37
BG58ASO 2014-06-18 10:01:42 10:06:09 00:04:27
HT56ENL 2014-06-18 10:02:21 10:07:27 00:05:06
X449LGS 2014-06-18 10:02:51 11:51:42 01:48:51
KR07DWW 2014-06-18 10:03:19 11:05:45 01:02:26
NA59FKE 2014-06-18 10:03:35 10:44:17 00:40:42
HV13MXY 2014-06-18 10:03:54 10:52:12 00:48:18
HS03FUW 2014-06-18 10:04:35 10:06:19 00:01:44
HX53FKP 2014-06-18 10:06:09 10:06:47 00:00:38
HK11NJU 2014-06-18 10:07:25 10:08:06 00:00:41
HN12OAV 2014-06-18 10:07:56 10:08:51 00:00:55
MM08AZB 2014-06-18 10:08:10 10:08:53 00:00:43
BW57ENK 2014-06-18 10:08:35 10:09:19 00:00:44
AE55LHR 2014-06-18 10:08:35 10:09:18 00:00:43
RV03HMA 2014-06-18 10:09:13 11:07:45 00:58:32
JPO6JEM 2014-06-18 10:10:48 10:11:25 00:00:37
BV62WMP 2014-06-18 10:11:00 10:11:46 00:00:46
X647HBP 2014-06-18 10:12:01 10:18:06 00:06:05
HV13LSJ 2014-06-18 10:12:18 10:12:54 00:00:36
X553UYC 2014-06-18 10:13:29 10:17:51 00:04:22
Y208WGO 2014-06-18 10:13:56 10:23:03 00:09:07
SQL command is below:
SELECT
A.plate,
a.date,
a.time as 'start time',
b.time as 'end time',
timediff(B.time, A.time) as 'Journey time'
FROM
(SELECT
x.plate,
date(x.timestamp) as 'date',
time(x.timestamp) as 'time',
COUNT(*) rank
FROM
anpr_1 x
JOIN anpr_1 y ON y.plate = x.plate
AND ((date(y.timestamp) < date(x.timestamp))
OR (date(y.timestamp) = date(x.timestamp)
AND time(y.timestamp) <= time(x.timestamp)))
GROUP BY x.plate , date(x.timestamp) , time(x.timestamp)) a
JOIN
(SELECT
x.plate,
date(x.timestamp) as 'Date',
time(x.timestamp) as 'time',
COUNT(*) rank
FROM
anpr_2 x
JOIN anpr_2 y ON y.plate = x.plate
AND ((date(y.timestamp) < date(x.timestamp))
OR (date(y.timestamp) = date(x.timestamp)
AND time(y.timestamp) <= time(x.timestamp)))
GROUP BY x.plate , date(x.timestamp) , time(x.timestamp)) b ON b.plate = a.plate AND b.rank = a.rank
where
b.time > a.time
and a.date = '2014-06-18'
and b.date = '2014-06-18'
and timediff(B.time, A.time) <= '03:00:00'
order by a.time;
While probably over complicated,This command works nicely for what I need. However what I am looking to do now is create another query that will aggregate these results into 15 minute time bins, with a median average, min and max Journey time, and a Count. so, for example, the 15 minutes of data shown above would appear as:
Timeslot Median Min Max Count
10:00:00 00:02:50 00:00:36 01:48:51 25
10:15:00 ??:??:?? ??:??:?? ??:??:?? ??
I have tried various combinations of group by and UNIX_TIMESTAMPs and looked at similar problems posted to this site, but I've not yet had much success in what I am trying to achieve. Is there anything that you could suggest to assist?
Here is a solution with average journey-time instead of median:
SELECT
`Date`,
FLOOR(
((60* 60 * HOUR(TIMEDIFF(Starttime, '10:00:00')))
+ (60 * MINUTE(TIMEDIFF(Starttime, '10:00:00')))
+ SECOND(TIMEDIFF(Starttime, '10:00:00')))/(15 * 60)) as intervalNo,
min(Journey_Time), max(Journey_Time),
SEC_TO_TIME(AVG(TIME_TO_SEC(Journey_Time))) as avgTime,
count(*) as Journeys
FROM table
GROUP BY 1,2
Be aware that this is based on 10:00:00 each day. Just change that to any other starttime. You can use MySQL ADDTIME() function to calculate an actual time-interval from the intervalNo (see here).
EDID
Found a compact Version with 15-min Timeslots:
SELECT
`Date`,
FLOOR(TIME_TO_SEC(TIMEDIFF(Starttime, '10:00:00'))/(15 * 60)) as intervalNo,
SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIMEDIFF(Starttime, '10:00:00'))/(15 * 60)) * 15 * 60)
as TimeInterval,
min(Journey_Time), max(Journey_Time),
SEC_TO_TIME(AVG(TIME_TO_SEC(Journey_Time))),
count(*) as Journeys
FROM table
GROUP BY 1,2