Select/Group by - Segments of Time (10 Seconds, 30 Seconds, etc)

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

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.

Group mysql query by 15 min intervals

SELECT   FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60)) AS timekey
FROM     table
GROUP BY timekey;

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

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.

How can I group time by hour or by 10 minutes?

finally done with

GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)

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

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.

MYSQL grouping a date interval by past 10 minutes

The problem is the WHERE clause. You need to remove the excess seconds before doing the aggregation.

One method is to convert to seconds, divide by 60, truncate the number, and multiply by 60. The following converts the value back to a date/time value, so an index on that column will be used:

SELECT date_format(timestamp, '%H:%i:%s'), COUNT(*) as count
FROM test
WHERE timestamp >= from_unixtime(floor(unix_timestamp(now()) / 60) * 60) - interval 10 minute AND
      timestamp < from_unixtime(floor(unix_timestamp(now()) / 60) * 60)
GROUP BY DATE_FORMAT(timestamp, '%i')
ORDER BY timestamp ASC

Query data to create statistical chart

To begin with, we know that we need to select warnings, then group them by a range of times. I am going to group by calendar week to start with.

All the warnings in a month:

SELECT `id` from warnings WHERE date_created >= 2019-07-01 AND date_created <= 2019-07-31;

To get HOW MANY warnings were in the month, it's almost the same:

SELECT count(`id`) from warnings WHERE date_created >= 2019-07-01 AND date_created <= 2019-07-31;

That will return one row with a single value in it. Not very interesting yet. To find out how many warnings happened each (calendar) week, you can group the results by the week.

SELECT count(`id`) as num_warnings, WEEK(date_created) as weeknum
FROM warning
WHERE `date_created` >= 2019-07-01 AND date_created <= 2019-07-31
GROUP BY weeknum;

This will give you the number of warnings in a calendar week. If the month started on a Friday, the first week will have a low number.

To query for seven-day intervals starting on the first of the month, things get a lot more complicated. (Also, obviously the last "week" won't be a full seven days.)

To help, I first referred to SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc) which talks about grouping by a number of seconds. A week is 60*60*24*7 seconds, so the answer can be converted pretty easily - but there's a catch we will get to.

SELECT count(`id`) as num_warnings as weeknum
FROM warning
WHERE `date_created` >= 2019-07-01 AND date_created <= 2019-07-31
GROUP BY UNIX_TIMESTAMP(date_created) DIV 604800

This takes the timestamp of the warning and divides it by the number of seconds in a week and chops off the decimal. So every 604800 seconds the division will increase by 1. Almost there, but here's the catch: this will tell you how many weeks it has been since January 1, 1973, and you want to know how many weeks it has been since the first of the month. Put another way, you want zero to be at the start of the month, not in 1973.

SELECT count(`id`) as num_warnings
FROM warnings
WHERE `date_created` >= 2019-07-01 AND date_created <= 2019-07-31
GROUP BY (UNIX_TIMESTAMP(date_created) - UNIX_TIMESTAMP('2019-07-01')) DIV 604800

That's pretty much it for dividing a month by weeks. I know almost nothing of Django, so I can't help you with the code that would generate the query.

But what about dividing a year by months? At first it seems like a similar problem, but there's a catch: How many seconds are there in a month?

The answer for grouping by month over a year is actually more like the original solution above for dividing by week. It works because the year always starts at the beginning of a month:

SELECT count(`id`) as num_warnings, MONTH(date_created) as monthNum
FROM warnings
WHERE `date_created` >= 2019-01-01 AND date_created <= 2019-12-31
GROUP BY monthNum;

Should get you close to where you want to go.

The two queries are different enough that you will want to recognize the different cases in your Django code and build the appropriate query.

---

Related Topics