1

UPDATE
I found this query :
SELECT CONCAT(DATE(gid), ' / ', HOUR(gdate), ':00:00 to ', CONCAT(HOUR(gdate), ':59:59')) as slice, COUNT(*) FROM t_table GROUP BY DATE(gdate), HOUR(gdate);

It doesnt output a ZERO when there is no data for a period of 8 hours or for a day. Is there a way ?

I have a table which has 3 fields :

  • gid (int)
  • gdate (datetime)
  • ginfo (text)

I want to find the correct query that would ouput the number of items (field ginfo) for every 8 hours every day between two dates. I am not sure if it's clear so I can try to schematize it :

The first date is : 2013/10/01.
The second date is : 2013/10/03.
For the first day the PHP output would be for example:
2013/10/01 00:00-07h59 : 8
2013/10/01 08:00-15h59 : 5
2013/10/01 16:00-23h59 : 7
For the second day the PHP output would be for example :
2013/10/01 00:00-07h59 : 2
2013/10/01 08:00-15h59 : 3
2013/10/01 16:00-23h59 : 1
etc.

In the table, there are 20 items for 2013/10/01. In the table, there are 6 items for 2013/10/02.

I can't find the correct query.
Can anyone help me ?
Excuse my poor english.

frontal
  • 99
  • 1
  • 1
  • 10
  • If there are no rows for the time period, should there be a row with a zero, or is no row OK? – Bohemian Oct 21 '13 at 03:54
  • can you give us sqlfiddle ? – Shafeeque Oct 21 '13 at 04:02
  • It would be better to find a ZERO. It would avoid to spend some time with PHP scripts if the query is well formated :=) – frontal Oct 21 '13 at 04:42
  • sqlfiddle for the creation of the table ? The table is quite simple (only 3 fields as I described above). For the query, I found something like that : http://stackoverflow.com/questions/15708027/mysql-select-hour-in-datetime-and-group. But there is no ZERO and it is done hour by hour. I need the 8 hour interval. – frontal Oct 21 '13 at 04:44
  • See the next post. I gave you 2 sqlfiddle. – frontal Oct 21 '13 at 18:33

3 Answers3

0

you can use like this :

SELECT COUNT(gid) FROM [table_name] WHERE (gdate>=2013/10/01 00:00) and (gdate<=2013/10/01 07:00)
UNION ALL 
SELECT COUNT(gid) FROM [table_name] WHERE (gdate>=2013/10/07 00:00) and (gdate<=2013/10/01 16:00)
UNION ALL
... bla bla with your conditions

i think the format date is not correct you can looking for format date.

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
Agoeng Liu
  • 674
  • 3
  • 10
  • 30
  • Thanks for your answer. I tried the query but it displays something like that : COUNT(gid) / 507 / 507 – frontal Oct 21 '13 at 04:39
0

Try like this.

SELECT COUNT(ginfo) 
FROM 'TABLE_NAME'
WHERE gdate BETWEEN 'from_date' AND 'to_date' 
GROUP BY (date(gdate) + INTERVAL 8 HOUR)
Sanal K
  • 723
  • 4
  • 14
  • Thanks for the answer. This is very closed to what I am looking for but there is two more small things. 1- I'd like to see the periode in the output (there is only the count). 2- Could there be a ZERO if there is no value ? (cf. Bohemian answer). Thanks – frontal Oct 21 '13 at 04:48
  • I checked well and there is a problem because I have entries from 00h00 to 23h59 for a day and in the output there is only one line for that day. The interval doesn't work ? – frontal Oct 21 '13 at 05:03
  • can you give the case in sqlfiddle ? – Sanal K Oct 21 '13 at 05:57
  • http://sqlfiddle.com/#!2/07486/3. In this case, I don't have a ZERO when there is no data in a specific time interval – frontal Oct 21 '13 at 18:30
  • http://sqlfiddle.com/#!2/018ab/1. In this case, I don't have the "8 hours interval" in my output. This is grouped by days. – frontal Oct 21 '13 at 18:32
0

You can try something like this 

SELECT ROUND(UNIX_TIMESTAMP(`gdate`)/(480 * 60)) AS timekey,count(*),`gdate`
FROM your_table
WHERE gdate BETWEEN '2013-10-01' AND '2013-10-03' 
GROUP BY timekey

You can refer following for detailed answer

GROUP BY interval overlapping timestamp MySQL query

Group mysql query by 15 min intervals

Community
  • 1
  • 1
Shafeeque
  • 2,039
  • 2
  • 13
  • 28
  • Thanks for the anwser. The output is quite strange for me. I don't know the notion of timekey. I'll check the URLs. Actually, it does not output what I really want. – frontal Oct 21 '13 at 04:52