SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc)

Related searches

I have a table (MySQL) that captures samples every n seconds. The table has many columns, but all that matters for this is two: a time stamp (of type TIMESTAMP) and a count (of type INT).

What I would like to do, is get sums and averages of the count column over a range of times. For instance, I have samples every 2 seconds recorded, but I would like the sum of the count column for all the samples in a 10 second or 30 second window for all samples.

Here's an example of the data:

+---------------------+-----------------+
| time_stamp          | count           |
+---------------------+-----------------+
| 2010-06-15 23:35:28 |               1 |
| 2010-06-15 23:35:30 |               1 |
| 2010-06-15 23:35:30 |               1 |
| 2010-06-15 23:35:30 |             942 |
| 2010-06-15 23:35:30 |             180 |
| 2010-06-15 23:35:30 |               4 |
| 2010-06-15 23:35:30 |              52 |
| 2010-06-15 23:35:30 |              12 |
| 2010-06-15 23:35:30 |               1 |
| 2010-06-15 23:35:30 |               1 |
| 2010-06-15 23:35:33 |            1468 |
| 2010-06-15 23:35:33 |             247 |
| 2010-06-15 23:35:33 |               1 |
| 2010-06-15 23:35:33 |              81 |
| 2010-06-15 23:35:33 |              16 |
| 2010-06-15 23:35:35 |            1828 |
| 2010-06-15 23:35:35 |             214 |
| 2010-06-15 23:35:35 |              75 |
| 2010-06-15 23:35:35 |               8 |
| 2010-06-15 23:35:37 |            1799 |
| 2010-06-15 23:35:37 |              24 |
| 2010-06-15 23:35:37 |              11 |
| 2010-06-15 23:35:37 |               2 |
| 2010-06-15 23:35:40 |             575 |
| 2010-06-15 23:35:40 |               1 |
| 2010-06-17 10:39:35 |               2 |
| 2010-06-17 10:39:35 |               2 |
| 2010-06-17 10:39:35 |               1 |
| 2010-06-17 10:39:35 |               2 |
| 2010-06-17 10:39:35 |               1 |
| 2010-06-17 10:39:40 |              35 |
| 2010-06-17 10:39:40 |              19 |
| 2010-06-17 10:39:40 |              37 |
| 2010-06-17 10:39:42 |              64 |
| 2010-06-17 10:39:42 |               3 |
| 2010-06-17 10:39:42 |              31 |
| 2010-06-17 10:39:42 |               7 |
| 2010-06-17 10:39:42 |             246 |
+---------------------+-----------------+

The output I would like (based on the data above) should look like this:

+---------------------+-----------------+
| 2010-06-15 23:35:00 |               1 |  # This is the sum for the 00 - 30 seconds range
| 2010-06-15 23:35:30 |            7544 |  # This is the sum for the 30 - 60 seconds range
| 2010-06-17 10:39:35 |             450 |  # This is the sum for the 30 - 60 seconds range
+---------------------+-----------------+

I have used GROUP BY to gather these numbers by the second, or by the minute, but I can't seem to figure out the syntax to get the sub-minute or range of seconds GROUP BY commands to work correctly.

I am mostly going to be using this query to syphon data from this table to another table.

Thanks!

GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 30

or say for some reason you wanted to group them in 20-second intervals it would be DIV 20 etc. To change the boundaries between GROUP BY values you could use

GROUP BY (UNIX_TIMESTAMP(time_stamp) + r) DIV 30

where r is a literal nonnegative integer less than 30. So

GROUP BY (UNIX_TIMESTAMP(time_stamp) + 5) DIV 30

should give you sums between hh:mm:05 and hh:mm:35 and between hh:mm:35 and hh:mm+1:05.

Ask TOM "group records by interval of 3 seconds", 10 5 loop 6 insert into t values ( l_date, dbms_random.value( 0, 100 ) ); 7 l_date case, group by rollup, group by cube, analytic functions, order by in a subquery ,etc are no idea how to use a DATE (point in time) as a duration here. you'll really Now, you don't really care if it take .05 seconds of 50 seconds as no humans� SELECT/GROUP BY-segments of time(10 seconds, 30 seconds, etc) (3) GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 30. or say for some reason you wanted to group them in 20-second intervals it would be DIV 20 etc.

I tried Hammerite's solution in my project, but it didn't work well where there were missing samples from the series. Here's an example of the query that is supposed to select timestamp (ts), user name and average measure from metric_table and group the results by 27-minute time intervals:

select 
    min(ts), 
    user_name, 
    sum(measure) / 27
from metric_table 
where 
    ts between date_sub('2015-03-17 00:00:00', INTERVAL 2160 MINUTE) and '2015-03-17 00:00:00' 

group by unix_timestamp(ts) div 1620, user_name 
order by ts, user_name
;

Note: 27 minutes (in select) = 1620 seconds (in group by), 2160 minutes = 3 days (that's the time range)

When I ran this query against a time series where samples were irregularly recorded (in other words: for any given time stamp there was no guarantee to find measure values for all user names) the results were not stamped according to the interval (were not placed every 27 minutes). I suspect that was due to min(ts) returning a time stamp in some groups that was greater than the expected floor(ts0 + i*interval). I modified the former query to this one:

select 
    from_unixtime(unix_timestamp(ts) - unix_timestamp(ts) mod 1620) as ts1, 
    user_name, 
    sum(measure) / 27
from metric_table
where 
    ts between date_sub('2015-03-17 00:00:00', INTERVAL 2160 MINUTE) and '2015-03-17 00:00:00' 

group by ts1, user_name 
order by ts1, user_name
;

and it works fine even when the samples are missing. I think that is because once the time math is moved to select it guarantees that ts1 will align with the time steps.

How do I return a group count for every 15 minutes of the hour on my , minutes of the hour? SELECT date_format( timestamp, '%h:00 %p' ) AS HOUR , count(… 900, of course, is 60*15 (60 seconds * 15 minutes). notice also the� 6 SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc) Dec 30 '19. View all questions (10) Meta Stack Exchange

Another solution.

To average over any interval you like you can convert your dt to timestamp and group by modulo by your interval (7 seconds in the example).

select FROM_UNIXTIME(
    UNIX_TIMESTAMP(dt_record) - UNIX_TIMESTAMP(dt_record) mod 7
) as dt, avg(1das4hrz) from `meteor-m2_msgi`
where dt_record>='2016-11-13 05:00:00'
and dt_record < '2016-11-13 05:02:00'
group by FROM_UNIXTIME(
    UNIX_TIMESTAMP(dt_record) - UNIX_TIMESTAMP(dt_record) mod 7);

To show how it works, I prepare a request, showing calculations.

select dt_record, minute(dt_record) as mm, SECOND(dt_record) as ss,
UNIX_TIMESTAMP(dt_record) as uxt, UNIX_TIMESTAMP(dt_record) mod 7 as ux7,
FROM_UNIXTIME(
    UNIX_TIMESTAMP(dt_record) - UNIX_TIMESTAMP(dt_record) mod 7) as dtsub,
column from `yourtable` where dt_record>='2016-11-13 05:00:00'
and dt_record < '2016-11-13 05:02:00';

+---------------------+--------------------+
| dt                  | avg(column)        |
+---------------------+--------------------+
| 2016-11-13 04:59:43 |  25434.85714285714 |
| 2016-11-13 05:00:42 |  5700.728813559322 |
| 2016-11-13 05:01:41 |  950.1016949152543 |
| 2016-11-13 05:02:40 |  4671.220338983051 |
| 2016-11-13 05:03:39 | 25468.728813559323 |
| 2016-11-13 05:04:38 |  43883.52542372881 |
| 2016-11-13 05:05:37 | 24589.338983050846 |
+---------------------+--------------------+


+---------------------+-----+-----+------------+------+---------------------+----------+
| dt_record           | mm  | ss  | uxt        | ux7  | dtsub               | column   |
+---------------------+------+-----+------------+------+---------------------+----------+
| 2016-11-13 05:00:00 |   0 |   0 | 1479002400 |    1 | 2016-11-13 04:59:59 |    36137 |
| 2016-11-13 05:00:01 |   0 |   1 | 1479002401 |    2 | 2016-11-13 04:59:59 |    36137 |
| 2016-11-13 05:00:02 |   0 |   2 | 1479002402 |    3 | 2016-11-13 04:59:59 |    36137 |
| 2016-11-13 05:00:03 |   0 |   3 | 1479002403 |    4 | 2016-11-13 04:59:59 |    34911 |     
| 2016-11-13 05:00:04 |   0 |   4 | 1479002404 |    5 | 2016-11-13 04:59:59 |    34911 |
| 2016-11-13 05:00:05 |   0 |   5 | 1479002405 |    6 | 2016-11-13 04:59:59 |    34911 |
| 2016-11-13 05:00:06 |   0 |   6 | 1479002406 |    0 | 2016-11-13 05:00:06 |    33726 |
| 2016-11-13 05:00:07 |   0 |   7 | 1479002407 |    1 | 2016-11-13 05:00:06 |    32581 |
| 2016-11-13 05:00:08 |   0 |   8 | 1479002408 |    2 | 2016-11-13 05:00:06 |    32581 |
| 2016-11-13 05:00:09 |   0 |   9 | 1479002409 |    3 | 2016-11-13 05:00:06 |    31475 |
+---------------------+-----+-----+------------+------+---------------------+----------+

Can anyone suggest something faster?

3 Ways to Group Times in Excel, Learn how to group times in a data set for summarized reports and The second column contains the date and time the transaction to group the times by every half hour “0:30” or every five minutes “0:05”. analyze it further to see the days of the week, weeks in the month, etc. 4/10/2017 7:21:23 AM 30 0 SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc) Nov 14 '16 0 strange Oracle error: “invalid format text” Jan 22 '12 0 why cx_Oracle and sqlplus give different results for the same select query Feb 28 '12

Very strange but using the solution here:

Average of data for every 5 minutes in the given times

We can suggest something like:

select convert(
              (min(dt_record) div 50)*50 - 20*((convert(min(dt_record), 
               datetime) div 50) mod 2), datetime)  as dt, 
       avg(1das4hrz) 
from `meteor-m2_msgi`
where dt_record>='2016-11-13 05:00:00'
       and dt_record < '2016-11-14 00:00:00' 
group by convert(dt_record, datetime) div 50;


select (
convert(
min(dt_record), datetime) div 50)*50 - 20*(
(convert(min(dt_record), datetime) div 50) mod 2
) as dt,
avg(column) from `your_table`
where dt_record>='2016-11-13 05:00:00'
and dt_record < '2016-11-14 00:00:00'
group by convert(dt_record, datetime) div 50;

50 is because 1/2 of NORMAL minute has 30 seconds while 'INTEGER DATE FORMAT' suppose us to divide by 50

Characteristics of EEG Interpreters Associated With Higher Interrater , A select group of the EEGers had good IRA and the other EEGers had low IRA. and to artifacts (extracerebral potentials from muscle, eyes, heart, electrodes, etc. ) Two hundred 30-second rEEG segments from 200 different rEEG studies The software displays 10 seconds of rEEG data at a time with channel labels. 83 MySQL Update query with left join and group by Jun 11 '10 72 Many-to-many relationships examples May 27 '10 69 SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc) Jun 21 '10

[PDF] Interactive Techniques (Kevin Yee), Think Break – Ask a rhetorical question, and then allow 20 seconds for Student Polling – Select some students to travel the room, polling the others 30. Background Knowledge Probe – Use questionnaire (multi-choice or short answer ) Name Game – Students form circles in groups of 8-10 and one at a time state their. Group time by minutes intervals. If you want to group time by minutes intervals, also can use formulas. Select a cell next to the time, and type this formula =FLOOR(A2,TIME(0,15,0)), A2 is the time you use, 15 is the minutes interval, press Enter key and drag fill handle down to apply this formula to cells.

The only new thing here is the “grouping” at STEP 2. We have an SQL clause for that. It’s called GROUP BY. Let’s see it in action: SELECT AVG(depdelay), origin FROM flight_delays GROUP BY origin; If you scroll through the results, you will see that there are some airports with an average departure delay of more than 30 or even 40 minutes.

The <snap-to-time> must include a relative_time_unit, the @ symbol, and a snap_to_time_unit. The offset, represented by the plus (+) or minus (-) is optional. If the <time_integer> is not specified, 1 is the default. For example if you specify w as the relative_time_unit, 1 week is assumed. The option is used only with the week timescale unit

Comments
  • Perfect! That did exactly what I needed! Thanks a bunch!
  • Complete query please. I cannot archive the OP result with this solution
  • Thanks for bringing this up, helped me out a lot!
  • Wonderful stuff! All I need now is a way for it to record a "zero" row for when there are no samples in that time bucket...
  • @DanielRhodes ever figure that one out?