Le regroupement dans l'intervalle de 5 minutes à l'intérieur d'un intervalle de temps

Question

J'ai quelques difficultés avec les commandes mySQL que je veux faire.

SELECT a.timestamp, name, count(b.name) 
FROM time a, id b 
WHERE a.user = b.user
  AND a.id = b.id
  AND b.name = 'John'
  AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00' 
GROUP BY a.timestamp

C'est ma sortie de courant de déclaration.

timestamp            name  count(b.name)
-------------------  ----  -------------
2010-11-16 10:32:22  John  2
2010-11-16 10:35:12  John  7
2010-11-16 10:36:34  John  1
2010-11-16 10:37:45  John  2
2010-11-16 10:48:26  John  8
2010-11-16 10:55:00  John  9
2010-11-16 10:58:08  John  2

Comment puis-je regrouper en 5 minutes d'intervalle résultats?

Je veux que ma sortie comme

timestamp            name  count(b.name)
-------------------  ----  -------------
2010-11-16 10:30:00  John  2
2010-11-16 10:35:00  John  10
2010-11-16 10:40:00  John  0
2010-11-16 10:45:00  John  8
2010-11-16 10:50:00  John  0
2010-11-16 10:55:00  John  11 

Answer 1

Cela fonctionne avec tous les intervalle

Postgresql

SELECT
    TIMESTAMP WITH TIME ZONE 'epoch' +
    INTERVAL '1 second' * round((extract('epoch' from timestamp) / 300) * 300 
    as timestamp,
    name,
    count(b.name)
FROM time a, id 
WHERE …
GROUP BY 
round(extract('epoch' from timestamp) / 300), name

Mysql

SELECT
    timestamp,  -- not sure about that
    name,
    count(b.name)
FROM time a, id 
WHERE …
GROUP BY 
round(UNIX_TIMESTAMP(timestamp) / 300), name

Answer 2

Vous devriez plutôt utiliser GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300 au lieu de ronde(../300) en raison de l'arrondissement, j'ai trouvé que certains documents sont comptés regroupés en deux ensembles de résultats.

Answer 3

Pour postgres, je l'ai trouvé plus facile et plus précis d'utiliser le

date_trunc

fonction, comme:

select name, sum(count), date_trunc('minute',timestamp) as timestamp
FROM table
WHERE xxx
GROUP BY name,date_trunc('minute',timestamp)
ORDER BY timestamp

Vous pouvez fournir diverses résolutions comme 'minute','heure','jour', etc... à date_trunc.

Answer 4

La requête sera quelque chose comme:

SELECT 
  DATE_FORMAT(
    MIN(timestamp),
    '%d/%m/%Y %H:%i:00'
  ) AS tmstamp,
  name,
  COUNT(id) AS cnt 
FROM
  table
GROUP BY ROUND(UNIX_TIMESTAMP(timestamp) / 300), name

Answer 5

Vous allez probablement avoir à casser votre timestamp dans ymd:HM et de l'utilisation des DIV à 5 de diviser le minutes en 5 minutes de bacs -- quelque chose comme

select year(a.timestamp), 
       month(a.timestamp), 
       hour(a.timestamp), 
       minute(a.timestamp) DIV 5,
       name, 
       count(b.name)
FROM time a, id b
WHERE a.user = b.user AND a.id = b.id AND b.name = 'John' 
      AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00'
GROUP BY year(a.timestamp), 
       month(a.timestamp), 
       hour(a.timestamp), 
       minute(a.timestamp) DIV 12

...et puis futz la sortie dans le code client à apparaître à la façon dont vous l'aimez. Ou, vous pouvez construire l'ensemble de la chaîne de date à l'aide de sql concat operatorinstead de se séparer les colonnes, si vous le souhaitez.

select concat(year(a.timestamp), "-", month(a.timestamp), "-" ,day(a.timestamp), 
       " " , lpad(hour(a.timestamp),2,'0'), ":", 
       lpad((minute(a.timestamp) DIV 5) * 5, 2, '0'))

...et puis groupe