CODEWITHSUNDEEP
mysqlsql
Liam Hendricks
Liam Hendricks

Reputation: 43

MySQL Moving daterange with set interval

This is what I have so far 

SELECT messageDatestamp 
     , count(messageDatestamp)  
  From table 
 where messageDatestamp between '2019-01-01 07:25:42' AND date_add('2019-01-01 07:25:45' , INTERVAL 1 SECOND)  
 group 
    by UNIX_TIMESTAMP(messageDatestamp)  
 ORDER 
    BY messageDatestamp asc;

messageDatestamp Column data

2019-01-01 07:25:43
2019-01-01 07:25:43
2019-01-01 07:25:43
2019-01-01 07:25:43
2019-01-01 07:25:43
2019-01-01 07:25:43
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:44
2019-01-01 07:25:45
2019-01-01 07:25:45
2019-01-01 07:25:45
2019-01-01 07:25:45
2019-01-01 07:25:45

Output

2019-01-01 07:25:43, 6
2019-01-01 07:25:44, 11
2019-01-01 07:25:45, 12

I don't want to execute a select statement for each daterange where the end date is increment every one second like the example below.

between '2019-01-01 07:25:42' and '2019-01-01 07:26:43'

between '2019-01-01 07:25:42' and '2019-01-01 07:26:44'

between '2019-01-01 07:25:42' and '2019-01-01 07:26:45'

Is there a way to increment the end date or add the previous count value using one query?

This is what I would like the MySQL to output.

2019-01-01 07:25:43, 6  (Count is between  07:25:42 and  07:25:43)
2019-01-01 07:25:44, 17 (Count is between  07:25:42 and  07:25:44)
2019-01-01 07:25:45, 22 (Count is between  07:25:42 and  07:25:45)

Upvotes: 1

Views: 94

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Is there a way to increment the end date or add the previous count value using one query?

You can use lag():

SELECT MIN(messageDatestamp), COUNT(*),
       (COUNT(*) + LAG(COUNT(*), 1, 0) OVER (ORDER BY MIN(messageDatestamp))
       ) as cnt_2
FROM table 
WHERE messageDatestamp BETWEEN '2019-01-01 07:25:42' AND date_add('2019-01-01 07:25:45' , INTERVAL 1 SECOND)  
GROUP BY UNIX_TIMESTAMP(messageDatestamp) 
ORDER BY messageDatestamp asc;

EDIT:

I see, I misunderstood your question. You simply want a cumulative count:

You can use lag():

SELECT MIN(messageDatestamp), COUNT(*),
       SUM(COUNT(*))OVER (ORDER BY MIN(messageDatestamp)) as cnt_2
FROM table 
WHERE messageDatestamp BETWEEN '2019-01-01 07:25:42' AND date_add('2019-01-01 07:25:45' , INTERVAL 1 SECOND)  
GROUP BY UNIX_TIMESTAMP(messageDatestamp) 
ORDER BY messageDatestamp asc;

Upvotes: 1

LukStorms
LukStorms

Reputation: 29667

In MySql 8.0 one can SUM OVER a normal COUNT.
With an ORDER BY that SUM OVER will calculate a cumulative total.

If the messageDatestamp is a TIMESTAMP(6) then to compare it could be truncated to the seconds. 

SELECT DATE_FORMAT(messageDatestamp,'%Y-%m-%d %H:%i:%s') AS messageDatestamp
,SUM(COUNT(*)) OVER (ORDER BY DATE_FORMAT(messageDatestamp,'%Y-%m-%d %H:%i:%s')) AS CumulativeCount
FROM `table` t
WHERE messageDatestamp >= TIMESTAMP('2019-01-01 07:25:42')
  AND messageDatestamp < TIMESTAMP('2019-01-01 07:25:45','00:00:01')
GROUP BY DATE_FORMAT(messageDatestamp,'%Y-%m-%d %H:%i:%s')
ORDER BY messageDatestamp ASC;

Using DATE_FORMAT here to truncate the timestamp to seconds.
Since UNIX_TIMESTAMP might cause a problem in the year 2038.

UPDATE

If messageDatestamp is a DATETIME(0) then it doesn't need to be truncated to the seconds.
Then the SQL can be golf-coded. 

SELECT messageDatestamp
,SUM(COUNT(*)) OVER (ORDER BY messageDatestamp) AS CumulativeCount
FROM `table` t
WHERE messageDatestamp >= '2019-01-01 07:25:42'
  AND messageDatestamp <= '2019-01-01 07:25:45'
GROUP BY messageDatestamp
ORDER BY messageDatestamp ASC;

A test on db<>fiddle here

Upvotes: 1

Related Questions