Agrupar datetime por medias horas?? se puede???

chelodelsur · #1 (**permalink**) 26/05/2009, 17:48

Hola

un gran saludo a todos.
Paso a mi consulta. Tengo una consulta que me trae datos agrupados por hora desde una campo datetime

Código Mysql:

Ver originalGroup By hour(fechahora)

Hasta aquí todo bien, pero ahora me pidieron sacar este reporte por cada media hora, e incluso cada 15 minutos.

Código mysql:

Ver originalGroup By mediahora(fechahora) ????

Alguna idea??

desde ya gracias

huesos52 · #2 (**permalink**) 26/05/2009, 18:49

No creo que se pueda.

Pero dinos con un ejemplo, que es exactamente lo que requieres y podemos mirar una solución.

Un saludo.

chelodelsur · #3 (**permalink**) 26/05/2009, 19:56

Esta consulta me trae la suma de contactos agrupados por:
Año, mes, día, fecha, hora.

Código mysql:

Ver originalSelect  
Year(fecha_date2) as `año`,    
upper(CASE   
WHEN MONTH(fecha_date2) = 1 THEN "enero"  
WHEN MONTH(fecha_date2) = 2 THEN "febrero" 
WHEN MONTH(fecha_date2) = 3 THEN "marzo"  
WHEN MONTH(fecha_date2) = 4 THEN "abril"  
WHEN MONTH(fecha_date2) = 5 THEN "mayo"  
WHEN MONTH(fecha_date2) = 6 THEN "junio"  
WHEN MONTH(fecha_date2) = 7 THEN "julio"  
WHEN MONTH(fecha_date2) = 8 THEN "agosto"  
WHEN MONTH(fecha_date2) = 9 THEN "septiembre"  
WHEN MONTH(fecha_date2) = 10 THEN "octubre"  
WHEN MONTH(fecha_date2) = 11 THEN "noviembre"  
WHEN MONTH(fecha_date2) = 12 THEN "diciembre"  
ELSE "NUUL_MES" END)  AS MES ,     
 CASE 
   WHEN weekday(fecha_date2) = 0 THEN "Lunes"
   WHEN weekday(fecha_date2) = 1 THEN "Martes"
   WHEN weekday(fecha_date2) = 2 THEN "Miercoles"
   WHEN weekday(fecha_date2) = 3 THEN "Jueves"
   WHEN weekday(fecha_date2) = 4 THEN "Viernes"
   WHEN weekday(fecha_date2) = 5 THEN "Sabado"
   WHEN weekday(fecha_date2) = 6 THEN "Domingo"
   ELSE "NUUL_DIA" END AS `DIA`,
date_format(fecha_date2,'%Y-%m-%d') as fecha,
hour(fecha_date2) as hora,
sum(if(recibido= '',0,recibido)) as Recibido
From Reg_15_minutos
Where fecha_date between '2009-03-01 00:00:00' and '2009-05-30 23:59:00'
group by year(fecha_date2), month(fecha_date2), dayofmonth(fecha_date2), hour(fecha_date2)
order by year(fecha_date2) asc, month(fecha_date2) asc, weekday(fecha_date2)asc, dayofmonth(fecha_date2) asc,hour(fecha_date2) asc

Y me regresa la siguiente tabla.
+------+-------+---------+------------+------+----------+
| año | MES | DIA | fecha | hora | Recibido |
+------+-------+---------+------------+------+----------+
| 2009 | MARZO | Domingo | 2009-03-01 | 4 | 9 |
| 2009 | MARZO | Domingo | 2009-03-01 | 5 | 9 |
| 2009 | MARZO | Domingo | 2009-03-01 | 6 | 14 |
| 2009 | MARZO | Domingo | 2009-03-01 | 7 | 7 |
+------+-------+---------+------------+------+----------+

Pero lo que necesito ahora es que me de lo recibido agrupado por año, mes, dia, fecha y cada media hora, o sea para el ejemplo:

4:00
4:30
5:00
5:30 etc

espero se entienda

Y por supúesto gracias por la ayuda

Chelodelsur

huesos52 · #4 (**permalink**) 26/05/2009, 21:36

No se si esto te sirva..
pruebalo y nos cuentas

Código mysql:

Ver originalSELECT  
YEAR(fecha_date2) as `año`,    
UPPER(CASE   
WHEN MONTH(fecha_date2) = 1 THEN "enero"  
WHEN MONTH(fecha_date2) = 2 THEN "febrero" 
WHEN MONTH(fecha_date2) = 3 THEN "marzo"  
WHEN MONTH(fecha_date2) = 4 THEN "abril"  
WHEN MONTH(fecha_date2) = 5 THEN "mayo"  
WHEN MONTH(fecha_date2) = 6 THEN "junio"  
WHEN MONTH(fecha_date2) = 7 THEN "julio"  
WHEN MONTH(fecha_date2) = 8 THEN "agosto"  
WHEN MONTH(fecha_date2) = 9 THEN "septiembre"  
WHEN MONTH(fecha_date2) = 10 THEN "octubre"  
WHEN MONTH(fecha_date2) = 11 THEN "noviembre"  
WHEN MONTH(fecha_date2) = 12 THEN "diciembre"  
ELSE "NUUL_MES" END)  AS MES ,     
 CASE 
   WHEN WEEKDAY(fecha_date2) = 0 THEN "Lunes"
   WHEN WEEKDAY(fecha_date2) = 1 THEN "Martes"
   WHEN WEEKDAY(fecha_date2) = 2 THEN "Miercoles"
   WHEN WEEKDAY(fecha_date2) = 3 THEN "Jueves"
   WHEN WEEKDAY(fecha_date2) = 4 THEN "Viernes"
   WHEN WEEKDAY(fecha_date2) = 5 THEN "Sabado"
   WHEN WEEKDAY(fecha_date2) = 6 THEN "Domingo"
   ELSE "NUUL_DIA" END AS `DIA`,
DATE_FORMAT(fecha_date2,'%Y-%m-%d') as fecha,
HOUR(fecha_date2) as hora,
MINUTE(fecha_date2) as minuto,
sum(if(recibido= '',0,recibido)) as Recibido
FROM Reg_15_minutos
WHERE fecha_date BETWEEN '2009-03-01 00:00:00' AND '2009-05-30 23:59:00'
GROUP BY YEAR(fecha_date2), MONTH(fecha_date2), DAYOFMONTH(fecha_date2), HOUR(fecha_date2), if(minute(fecha_date2)<=30,1,0)
ORDER BY YEAR(fecha_date2) asc, MONTH(fecha_date2) asc, WEEKDAY(fecha_date2)asc, DAYOFMONTH(fecha_date2) asc,HOUR(fecha_date2) asc, minute(fecha_date2) asc;

Por lo menos agrupa en dos grupos (antes de 30 min y despues de 30 min)

jurena · #5 (**permalink**) 27/05/2009, 03:08

Mira aquí, por si te sirve de algo:
http://www.forosdelweb.com/f86/agrup...inutos-595591/

y luego para mostrar la hora así
12:00
12:30
etc.
multiplicas el número que sale por 60 y lo conviertes en tiempo, o bien creas una tabla de correspondencias...

chelodelsur · #6 (**permalink**) 27/05/2009, 06:23

MUCHAS GRACIAS a Huesos52 y a Jurena

He visto las dos propuestas y ambas me ayudan, creo que la de Juerena se acerca más a mi solución. Solo tengo que salvar algunas dificultades de mis datos, tales como que pasa cuando en un perido (30 o 15 min) no hay registros.

Muchas gracias me han ayudado un monton.

Chelodelsur

jurena · #7 (**permalink**) 27/05/2009, 08:06

No tiene por qué pasar nada; si usas la solución de la tabla (con las medias horas posibles y el número de agrupación: serían dos campos) relacionada con el valor generado haces un right join desde el valor que obtienes a esa tabla, relacionado por el número de agrupación, y utilizas un ifnull() para el valor generado, haciendo que sea 0 para en el caso de ser null el campo generado por tu fecha, y ya debería estar.

huesos52 · #8 (**permalink**) 27/05/2009, 08:17

Excelente Jurena.

Todos los días aprendo algo nuevo.
Y lo que creía imposible, es posible. jejeje

chelodelsur · #9 (**permalink**) 21/07/2009, 13:59

Hola a todos
Yo de nuevo..

He tenido que retomar este tema que se ma había quedado en el tintero, y no doy con la solución a mi problema.
El siguiente codigo me lista los datos, ya (con la ayuda de Jurena y Huesos52) he formateado las horas a periodos de media hora, pero no logro levantar las horas donde no hay registro.

Código MySQL:

Ver originalSELECT  
      YEAR(fecha_date2) AS `año`,    
      UPPER(CASE WHEN MONTH(fecha_date2) = 1 THEN "enero"  
                 WHEN MONTH(fecha_date2) = 2 THEN "febrero" 
                 WHEN MONTH(fecha_date2) = 3 THEN "marzo"  
                 WHEN MONTH(fecha_date2) = 4 THEN "abril"  
                 WHEN MONTH(fecha_date2) = 5 THEN "mayo"  
                 WHEN MONTH(fecha_date2) = 6 THEN "junio"  
                 WHEN MONTH(fecha_date2) = 7 THEN "julio"  
                 WHEN MONTH(fecha_date2) = 8 THEN "agosto"  
                 WHEN MONTH(fecha_date2) = 9 THEN "septiembre"  
                 WHEN MONTH(fecha_date2) = 10 THEN "octubre"  
                 WHEN MONTH(fecha_date2) = 11 THEN "noviembre"  
                 WHEN MONTH(fecha_date2) = 12 THEN "diciembre"  
                               ELSE "NUUL_MES" END)  AS MES ,     
      UPPER(CASE WHEN WEEKDAY(fecha_date2) = 0 THEN "Lunes"
                 WHEN WEEKDAY(fecha_date2) = 1 THEN "Martes"
                 WHEN WEEKDAY(fecha_date2) = 2 THEN "Miercoles"
                 WHEN WEEKDAY(fecha_date2) = 3 THEN "Jueves"
                 WHEN WEEKDAY(fecha_date2) = 4 THEN "Viernes"
                 WHEN WEEKDAY(fecha_date2) = 5 THEN "Sabado"
                 WHEN WEEKDAY(fecha_date2) = 6 THEN "Domingo"
                                ELSE "NUUL_DIA" END) AS `DIA`,
      DATE_FORMAT(fecha_date2,'%Y-%m-%d') AS fecha,
      TIME_FORMAT(fecha_date2,'%H:%i:%s') AS Horareal,
       h.mediahora AS `mediahora`,  
            SUM(IF(recibido= '',0,recibido)) AS Recibido
FROM rrserver.Reg_15_minutos 
RIGHT JOIN rrserver.horas h
ON  h.mediahora =  CONCAT(IF(HOUR(fecha_date2)<10,CONCAT('0',HOUR(fecha_date2)) , HOUR(fecha_date2)),':', IF(MINUTE(fecha_date2)<29,'00','30'),':','00')   
WHERE fecha_date2 BETWEEN '2009-03-02 00:00:00' AND '2009-03-02 100:00'
GROUP BY YEAR(fecha_date2), MONTH(fecha_date2), DAYOFMONTH(fecha_date2), `mediahora`
 
ORDER BY YEAR(fecha_date2) ASC, MONTH(fecha_date2) ASC, WEEKDAY(fecha_date2)ASC, DAYOFMONTH(fecha_date2) ASC, h.mediahora ASC;

Como ven este es el resultado y por ejemplo no levanta el periodo de las 03:30, o 04:30, 05:00, 05:30. por que en esos hoarios no hay registros.

Porfa denme una mano una vez más
+------+-------+-------+------------+----------+-----------+----------+
| año | MES | DIA | fecha | Horareal | mediahora | Recibido |
+------+-------+-------+------------+----------+-----------+----------+
| 2009 | MARZO | LUNES | 2009-03-02 | 00:15:00 | 00:00:00 | 2 |
| 2009 | MARZO | LUNES | 2009-03-02 | 00:45:00 | 00:30:00 | 1 |
| 2009 | MARZO | LUNES | 2009-03-02 | 01:00:00 | 01:00:00 | 3 |
| 2009 | MARZO | LUNES | 2009-03-02 | 01:30:00 | 01:30:00 | 1 |
| 2009 | MARZO | LUNES | 2009-03-02 | 02:15:00 | 02:00:00 | 1 |
| 2009 | MARZO | LUNES | 2009-03-02 | 02:30:00 | 02:30:00 | 2 |
| 2009 | MARZO | LUNES | 2009-03-02 | 03:00:00 | 03:00:00 | 2 |
| 2009 | MARZO | LUNES | 2009-03-02 | 04:15:00 | 04:00:00 | 2 |
| 2009 | MARZO | LUNES | 2009-03-02 | 06:15:00 | 06:00:00 | 3 |
| 2009 | MARZO | LUNES | 2009-03-02 | 06:30:00 | 06:30:00 | 7 |
| 2009 | MARZO | LUNES | 2009-03-02 | 07:00:00 | 07:00:00 | 24 |
| 2009 | MARZO | LUNES | 2009-03-02 | 07:30:00 | 07:30:00 | 62 |
| 2009 | MARZO | LUNES | 2009-03-02 | 08:00:00 | 08:00:00 | 101 |
| 2009 | MARZO | LUNES | 2009-03-02 | 08:30:00 | 08:30:00 | 98 |
| 2009 | MARZO | LUNES | 2009-03-02 | 09:00:00 | 09:00:00 | 142 |
| 2009 | MARZO | LUNES | 2009-03-02 | 09:30:00 | 09:30:00 | 122 |
| 2009 | MARZO | LUNES | 2009-03-02 | 10:00:00 | 10:00:00 | 85 |
+------+-------+-------+------------+----------+-----------+----------+

desde ya gracias

huesos52 · #10 (**permalink**) 21/07/2009, 14:32

No recuerdo muy bien este tema

Pero acomodandome un poco al ultimo post propuesto por jurena, podrías probar algo así:

Código sql:

Ver originalTIME_FORMAT(fecha_date2,'%H:%i:%s') AS Horareal,
       ifnull(h.mediahora,0) AS `mediahora`,  
            SUM(IF(recibido= '',0,recibido)) AS Recibido
FROM rrserver.Reg_15_minutos

Si no es lo que requieres, te agradecería postearas las sentencias create table de reg_15_minutos y horas. Ademas, unos cuantos inserts para poder hacer pruebas.

Un saludo.

chelodelsur · #11 (**permalink**) 22/07/2009, 06:38

Gracias por el interes Huesos52

Mira tu codigo no soluciona mi tema, lo que pasa es que en la tabla reg_15_minutos, se guardan los contactos entrantes, la aplicación hace un Insert en la tabla cada 15 minutos, pero por un tema de diseño de la aplicación, para ahorrar espacio de disco, cuando en un periodo de tiempo no hay registros, la aplicación NO hace el insert, por lo que al consultar los datos, solo levanta los horarios en los que se registraron contactos. Bien necesito hacer una curva de trafico de esos datos, pero necesito graficar todas las horas del día, y para las horas en las que no hay registro colocar un valor 0 (cero).
Hice otra tabla con las horas del día, en intervalos de media hora, e intento hacer un cruce, para que me liste todas las horas del dia, pero no logro hacerlo. De momento el codigo que tengo hace correctamente el cruce de las dos tablas, pero no levanta los horarios de la tabla horas en los que no hay registros en la tabla Reg_15_minutos.

Código MySQL:

Ver originalCREATE TABLE `reg_15_minutos` (
  `NOMBRE_SITIO` VARCHAR(10) DEFAULT NULL,
  `Fecha_date2` DATETIME DEFAULT NULL,
  `RECIBIDO` DECIMAL(10,0) DEFAULT NULL,
  ) ENGINE=INNODB DEFAULT CHARSET=latin1

Ejemplo de datos de esta tabla

Código MYSQL:

Ver originalinsert into `reg_15_minutos` (`nombre_sitio`, `fecha_date2`, `recibido`) values('PRUEBA','2009-03-02 00:15:00','2');
insert into `reg_15_minutos` (`nombre_sitio`, `fecha_date2`, `recibido`) values('PRUEBA','2009-03-02 01:00:00','1');
insert into `reg_15_minutos` (`nombre_sitio`, `fecha_date2`, `recibido`) values('PRUEBA','2009-03-02 02:15:00','1');
insert into `reg_15_minutos` (`nombre_sitio`, `fecha_date2`, `recibido`) values('PRUEBA','2009-03-02 03:00:00','1');
insert into `reg_15_minutos` (`nombre_sitio`, `fecha_date2`, `recibido`) values('PRUEBA','2009-03-02 04:15:00','1');
insert into `reg_15_minutos` (`nombre_sitio`, `fecha_date2`, `recibido`) values('PRUEBA','2009-03-02 06:15:00','2');
insert into `reg_15_minutos` (`nombre_sitio`, `fecha_date2`, `recibido`) values('PRUEBA','2009-03-02 07:00:00','8');
insert into `reg_15_minutos` (`nombre_sitio`, `fecha_date2`, `recibido`) values('PRUEBA','2009-03-02 08:00:00','5');

Código MySQL:

Ver originalCREATE TABLE `horas` (
  `mediahora` TIME NOT NULL DEFAULT '00:00:00',
  `orden` VARCHAR(4) DEFAULT NULL
) ENGINE=MYISAM DEFAULT CHARSET=latin1

Datos de la tabla Horas, la columna orden esra un idea para relacionarlos con los horas, pero no cumple otra funcion.

Código MySQL:

Ver originalinsert into `horas` (`mediahora`, `orden`) values('00:00:00','1 ');
insert into `horas` (`mediahora`, `orden`) values('00:30:00','2 ');
insert into `horas` (`mediahora`, `orden`) values('01:00:00','3 ');
insert into `horas` (`mediahora`, `orden`) values('01:30:00','4 ');
insert into `horas` (`mediahora`, `orden`) values('02:00:00','5 ');
insert into `horas` (`mediahora`, `orden`) values('02:30:00','6 ');
insert into `horas` (`mediahora`, `orden`) values('03:00:00','7 ');
insert into `horas` (`mediahora`, `orden`) values('03:30:00','8 ');
insert into `horas` (`mediahora`, `orden`) values('04:00:00','9 ');
insert into `horas` (`mediahora`, `orden`) values('04:30:00','10 ');
insert into `horas` (`mediahora`, `orden`) values('05:00:00','11 ');
insert into `horas` (`mediahora`, `orden`) values('05:30:00','12 ');
insert into `horas` (`mediahora`, `orden`) values('06:00:00','13 ');
insert into `horas` (`mediahora`, `orden`) values('06:30:00','14 ');
insert into `horas` (`mediahora`, `orden`) values('07:00:00','15 ');
insert into `horas` (`mediahora`, `orden`) values('07:30:00','16 ');
insert into `horas` (`mediahora`, `orden`) values('08:00:00','17 ');

Bien perdonen lo extenso del post, desde ya gracias por la ayuda.

Chelodelsur

huesos52 · #12 (**permalink**) 22/07/2009, 08:00

chelodelsur..

Vaya si fue dificil encontrar el por que no respetaba el left o right join al momento de hacer la consulta.
Todo se da por la condición que se maneja en el where. Desde ahí, estamos limitando toda una consulta con referencia a el campo fecha_date2 y no teniendo en cuenta a mediahora de la tabla horas.

Código mysql:

Ver originalSELECT  
      ifnull(YEAR(fecha_date2),"SIN REGISTROS") AS `año`,    
      UPPER(CASE WHEN MONTH(fecha_date2) = 1 THEN "enero"  
                 WHEN MONTH(fecha_date2) = 2 THEN "febrero" 
                 WHEN MONTH(fecha_date2) = 3 THEN "marzo"  
                 WHEN MONTH(fecha_date2) = 4 THEN "abril"  
                 WHEN MONTH(fecha_date2) = 5 THEN "mayo"  
                 WHEN MONTH(fecha_date2) = 6 THEN "junio"  
                 WHEN MONTH(fecha_date2) = 7 THEN "julio"  
                 WHEN MONTH(fecha_date2) = 8 THEN "agosto"  
                 WHEN MONTH(fecha_date2) = 9 THEN "septiembre"  
                 WHEN MONTH(fecha_date2) = 10 THEN "octubre"  
                 WHEN MONTH(fecha_date2) = 11 THEN "noviembre"  
                 WHEN MONTH(fecha_date2) = 12 THEN "diciembre"  
                               ELSE "Sin registros" END)  AS MES ,     
      UPPER(CASE WHEN WEEKDAY(fecha_date2) = 0 THEN "Lunes"
                 WHEN WEEKDAY(fecha_date2) = 1 THEN "Martes"
                 WHEN WEEKDAY(fecha_date2) = 2 THEN "Miercoles"
                 WHEN WEEKDAY(fecha_date2) = 3 THEN "Jueves"
                 WHEN WEEKDAY(fecha_date2) = 4 THEN "Viernes"
                 WHEN WEEKDAY(fecha_date2) = 5 THEN "Sabado"
                 WHEN WEEKDAY(fecha_date2) = 6 THEN "Domingo"
                                ELSE "Sin registros" END) AS `DIA`,
      ifnull(DATE_FORMAT(fecha_date2,'%Y-%m-%d'),"SIN REGISTROS") AS fecha,
      ifnull(TIME_FORMAT(fecha_date2,'%H:%i:%s'),"SIN REGISTROS") AS Horareal,
      h.mediahora AS `mediahora`,  
      ifnull(SUM(IF(recibido= '',0,recibido)),"SIN REGISTROS") AS Recibido
FROM Reg_15_minutos 
RIGHT JOIN horas h
ON  h.mediahora =  CONCAT(IF(HOUR(fecha_date2)<10,CONCAT('0',HOUR(fecha_date2)) , HOUR(fecha_date2)),':', IF(MINUTE(fecha_date2)<29,'00','30'),':','00')   
WHERE (fecha_date2 BETWEEN '2009-03-02 00:00:00' AND '2009-03-02 09:00:00') OR (h.mediahora between time('2009-03-02 00:00:00') and time('2009-03-02 09:00:00'))
GROUP BY YEAR(fecha_date2), MONTH(fecha_date2), DAYOFMONTH(fecha_date2), `mediahora`
ORDER BY YEAR(fecha_date2) ASC, MONTH(fecha_date2) ASC, WEEKDAY(fecha_date2)ASC, DAYOFMONTH(fecha_date2) ASC, h.mediahora ASC;

He probado esto y parece funcionar. Pruebalo y nos cuentas.

chelodelsur · #13 (**permalink**) 22/07/2009, 08:45

no funciona

Solo levanta las horas en las que hay datos. si te fijas por ejemplo levanta a las:

00:00 = 2,
no levanta las 00:30= 0,
01:00 = 1,

MM esta complicado

huesos52 · #14 (**permalink**) 22/07/2009, 08:52

No te entiendo chelo del sur.
Mira esto:

Código mysql:

Ver originalmysql> SELECT
    ->       ifnull(YEAR(fecha_date2),"SIN REGISTROS") AS `año`,
    ->       UPPER(CASE WHEN MONTH(fecha_date2) = 1 THEN "enero"
    ->                  WHEN MONTH(fecha_date2) = 2 THEN "febrero"
    ->                  WHEN MONTH(fecha_date2) = 3 THEN "marzo"
    ->                  WHEN MONTH(fecha_date2) = 4 THEN "abril"
    ->                  WHEN MONTH(fecha_date2) = 5 THEN "mayo"
    ->                  WHEN MONTH(fecha_date2) = 6 THEN "junio"
    ->                  WHEN MONTH(fecha_date2) = 7 THEN "julio"
    ->                  WHEN MONTH(fecha_date2) = 8 THEN "agosto"
    ->                  WHEN MONTH(fecha_date2) = 9 THEN "septiembre"
    ->                  WHEN MONTH(fecha_date2) = 10 THEN "octubre"
    ->                  WHEN MONTH(fecha_date2) = 11 THEN "noviembre"
    ->                  WHEN MONTH(fecha_date2) = 12 THEN "diciembre"
    ->                                ELSE "Sin registros" END)  AS MES ,
    ->       UPPER(CASE WHEN WEEKDAY(fecha_date2) = 0 THEN "Lunes"
    ->                  WHEN WEEKDAY(fecha_date2) = 1 THEN "Martes"
    ->                  WHEN WEEKDAY(fecha_date2) = 2 THEN "Miercoles"
    ->                  WHEN WEEKDAY(fecha_date2) = 3 THEN "Jueves"
    ->                  WHEN WEEKDAY(fecha_date2) = 4 THEN "Viernes"
    ->                  WHEN WEEKDAY(fecha_date2) = 5 THEN "Sabado"
    ->                  WHEN WEEKDAY(fecha_date2) = 6 THEN "Domingo"
    ->                                 ELSE "Sin registros" END) AS `DIA`,
    ->       ifnull(DATE_FORMAT(fecha_date2,'%Y-%m-%d'),"SIN REGISTROS") AS fecha,
    ->       ifnull(TIME_FORMAT(fecha_date2,'%H:%i:%s'),"SIN REGISTROS") AS Horareal,
    ->       h.mediahora AS `mediahora`,
    ->       ifnull(SUM(IF(recibido= '',0,recibido)),0) AS Recibido
    -> FROM Reg_15_minutos
    -> RIGHT JOIN horas h
    -> ON  h.mediahora =  CONCAT(IF(HOUR(fecha_date2)<10,CONCAT('0',HOUR(fecha_date2)) , HOUR(fecha_date2)),':', IF(MINUTE(fecha_date2)<29,'00','30'),':','00')
    -> WHERE (fecha_date2 BETWEEN '2009-03-02 00:00:00' AND '2009-03-02 09:00:00') OR (h.mediahora between time('2009-03-02 00:00:00') and time('2009-03-02 09:00:00'))
    -> GROUP BY YEAR(fecha_date2), MONTH(fecha_date2), DAYOFMONTH(fecha_date2), `mediahora`
    -> ORDER BY h.mediahora,fecha_date2;
+---------------+---------------+---------------+---------------+---------------+-----------+----------+
| a&#241;o           | MES           | DIA           | fecha         | Horareal      | mediahora | Recibido |
+---------------+---------------+---------------+---------------+---------------+-----------+----------+
| 2009          | MARZO         | LUNES         | 2009-03-02    | 00:15:00      | 00:00:00  |        5 |
| SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | 00:30:00  |        0 |
| 2009          | MARZO         | LUNES         | 2009-03-02    | 01:00:00      | 01:00:00  |        1 |
| SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | 01:30:00  |        0 |
| 2009          | MARZO         | LUNES         | 2009-03-02    | 02:15:00      | 02:00:00  |        1 |
| SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | 02:30:00  |        0 |
| 2009          | MARZO         | LUNES         | 2009-03-02    | 03:00:00      | 03:00:00  |        1 |
| SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | 03:30:00  |        0 |
| 2009          | MARZO         | LUNES         | 2009-03-02    | 04:15:00      | 04:00:00  |        1 |
| SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | 04:30:00  |        0 |
| SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | 05:00:00  |        0 |
| SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | 05:30:00  |        0 |
| 2009          | MARZO         | LUNES         | 2009-03-02    | 06:15:00      | 06:00:00  |        2 |
| SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | 06:30:00  |        0 |
| 2009          | MARZO         | LUNES         | 2009-03-02    | 07:00:00      | 07:00:00  |        8 |
| SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | SIN REGISTROS | 07:30:00  |        0 |
| 2009          | MARZO         | LUNES         | 2009-03-02    | 08:00:00      | 08:00:00  |        5 |
+---------------+---------------+---------------+---------------+---------------+-----------+----------+
17 rows in set (0.00 sec)
 
mysql>

Muestra todas las horas presentes en la tabla horas y dice si hay o no registros. Realmente, que salida esperarías?

chelodelsur · #15 (**permalink**) 22/07/2009, 12:47

. Me parece que en la tabla de prueba lo hace por que hay datos de algunas horas de UN SOLO día, pero el la realidad la tabla tiene datos de varios meses, por lo que por ejemplo el día 03 no tiene registros a las 04 am, pero el 4 si,

En fin , estoy viendo otra manera, pero no me termina de convencer.

Huesos52 , agradesco sinceramente tu colaboración

, luego posteo el otro codigo para tu opinión y contribución

Gracias.

chelodelsur · #16 (**permalink**) 22/07/2009, 13:13

Huesos52:

Mira esta es la consulta nueva, (por decirlo, ya que es la misma anterior repetida dos veces, con algunos ajustes)

Código MySql:

Ver originalSELECT año,mes,dia,fecha,mediahora,SUM(Recibido)
FROM 
(
SELECT  
      YEAR(k.fecha_date2) AS año,    
      UPPER(CASE WHEN MONTH(k.fecha_date2) = 1 THEN "enero"  
                 WHEN MONTH(k.fecha_date2) = 2 THEN "febrero" 
                 WHEN MONTH(k.fecha_date2) = 3 THEN "marzo"  
                 WHEN MONTH(k.fecha_date2) = 4 THEN "abril"  
                 WHEN MONTH(k.fecha_date2) = 5 THEN "mayo"  
                 WHEN MONTH(k.fecha_date2) = 6 THEN "junio"  
                 WHEN MONTH(k.fecha_date2) = 7 THEN "julio"  
                 WHEN MONTH(k.fecha_date2) = 8 THEN "agosto"  
                 WHEN MONTH(k.fecha_date2) = 9 THEN "septiembre"  
                 WHEN MONTH(k.fecha_date2) = 10 THEN "octubre"  
                 WHEN MONTH(k.fecha_date2) = 11 THEN "noviembre"  
                 WHEN MONTH(k.fecha_date2) = 12 THEN "diciembre"  
                               ELSE "NUUL_MES" END)  AS MES ,     
      UPPER(CASE WHEN WEEKDAY(k.fecha_date2) = 0 THEN "Lunes"
                 WHEN WEEKDAY(k.fecha_date2) = 1 THEN "Martes"
                 WHEN WEEKDAY(k.fecha_date2) = 2 THEN "Miercoles"
                 WHEN WEEKDAY(k.fecha_date2) = 3 THEN "Jueves"
                 WHEN WEEKDAY(k.fecha_date2) = 4 THEN "Viernes"
                 WHEN WEEKDAY(k.fecha_date2) = 5 THEN "Sabado"
                 WHEN WEEKDAY(k.fecha_date2) = 6 THEN "Domingo"
                                ELSE "NUUL_DIA" END) AS `DIA`,
      DATE_FORMAT(k.fecha_date2,'%Y-%m-%d') AS fecha,
      TIME_FORMAT(k.fecha_date2,'%H:%i:%s') AS Horareal,
       j.mediahora AS mediahora,  
            0 AS Recibido
FROM rrserver.horas j,
rrserver.reg_15_minutos k
WHERE k.fecha_date2 BETWEEN '2009-03-02 00:00:00' AND '2009-07-19 23:59'
 
UNION ALL
 
SELECT  
      YEAR(t.fecha_date2) AS año,    
      UPPER(CASE WHEN MONTH(t.fecha_date2) = 1 THEN "enero"  
                 WHEN MONTH(t.fecha_date2) = 2 THEN "febrero" 
                 WHEN MONTH(t.fecha_date2) = 3 THEN "marzo"  
                 WHEN MONTH(t.fecha_date2) = 4 THEN "abril"  
                 WHEN MONTH(t.fecha_date2) = 5 THEN "mayo"  
                 WHEN MONTH(t.fecha_date2) = 6 THEN "junio"  
                 WHEN MONTH(t.fecha_date2) = 7 THEN "julio"  
                 WHEN MONTH(t.fecha_date2) = 8 THEN "agosto"  
                 WHEN MONTH(t.fecha_date2) = 9 THEN "septiembre"  
                 WHEN MONTH(t.fecha_date2) = 10 THEN "octubre"  
                 WHEN MONTH(t.fecha_date2) = 11 THEN "noviembre"  
                 WHEN MONTH(t.fecha_date2) = 12 THEN "diciembre"  
                               ELSE "NUUL_MES" END)  AS MES ,     
      UPPER(CASE WHEN WEEKDAY(t.fecha_date2) = 0 THEN "Lunes"
                 WHEN WEEKDAY(t.fecha_date2) = 1 THEN "Martes"
                 WHEN WEEKDAY(t.fecha_date2) = 2 THEN "Miercoles"
                 WHEN WEEKDAY(t.fecha_date2) = 3 THEN "Jueves"
                 WHEN WEEKDAY(t.fecha_date2) = 4 THEN "Viernes"
                 WHEN WEEKDAY(t.fecha_date2) = 5 THEN "Sabado"
                 WHEN WEEKDAY(t.fecha_date2) = 6 THEN "Domingo"
                                ELSE "NUUL_DIA" END) AS `DIA`,
      DATE_FORMAT(t.fecha_date2,'%Y-%m-%d') AS fecha,
      TIME_FORMAT(t.fecha_date2,'%H:%i:%s') AS Horareal,
       h.mediahora AS mediahora,  
            (IF(t.recibido= '',0,t.recibido)) AS Recibido
FROM rrserver.reg_15_minutos t
LEFT JOIN rrserver.horas h
ON CONCAT(IF(HOUR(t.fecha_date2)<10,CONCAT('0',HOUR(t.fecha_date2)) , HOUR(t.fecha_date2)),':', IF(MINUTE(t.fecha_date2)<29,'00','30'),':','00') =  h.mediahora
WHERE t.fecha_date2 BETWEEN '2009-03-02 00:00:00' AND '2009-07-19 23:59'
) a
GROUP BY año,mes,dia,fecha,mediahora;

Hago una consulta que en el From tiene dos consultas unidas por UNION ALL, La primera me trae todas las horas de la tabla horas, luego con la segunda consulta sumo los registros de recibidos en la tabla reg_15_min. Con UNION ALL junto las consultas y me entrega los datos como los necesito.
Ventajas: hace el trabajo,
Desventajas: es lenta (no em complica mucho eso), es suceptible a errores de ingreso al ser tan largo el codigo, no me deja ordenar en el "sentido" de fecha de los campos del select principal, ya que los ordena en base a texto, ya que son los alias de las consultas anidadas. No se ve muy elegante, jajjaj ...
Pero funciona, por lo menos hasta el momento me parece que hace todo lo que necesito.

Código:

+------+-------+-------+------------+-----------+---------------+
| año | mes   | dia   | fecha      | mediahora | SUM(Recibido) |
+------+-------+-------+------------+-----------+---------------+
| 2009 | MARZO | LUNES | 2009-03-02 | 00:00:00  |             2 |
| 2009 | MARZO | LUNES | 2009-03-02 | 00:30:00  |             1 |
| 2009 | MARZO | LUNES | 2009-03-02 | 01:00:00  |             3 |
| 2009 | MARZO | LUNES | 2009-03-02 | 01:30:00  |             1 |
| 2009 | MARZO | LUNES | 2009-03-02 | 02:00:00  |             1 |
| 2009 | MARZO | LUNES | 2009-03-02 | 02:30:00  |             2 |
| 2009 | MARZO | LUNES | 2009-03-02 | 03:00:00  |             2 |
| 2009 | MARZO | LUNES | 2009-03-02 | 03:30:00  |             0 |
| 2009 | MARZO | LUNES | 2009-03-02 | 04:00:00  |             2 |
| 2009 | MARZO | LUNES | 2009-03-02 | 04:30:00  |             0 |
| 2009 | MARZO | LUNES | 2009-03-02 | 05:00:00  |             0 |
| 2009 | MARZO | LUNES | 2009-03-02 | 05:30:00  |             0 |
| 2009 | MARZO | LUNES | 2009-03-02 | 06:00:00  |             3 |
| 2009 | MARZO | LUNES | 2009-03-02 | 06:30:00  |             7 |
| 2009 | MARZO | LUNES | 2009-03-02 | 07:00:00  |            24 |
| 2009 | MARZO | LUNES | 2009-03-02 | 07:30:00  |            62 |
| 2009 | MARZO | LUNES | 2009-03-02 | 08:00:00  |            49 |
| 2009 | MARZO | LUNES | 2009-03-02 | 08:30:00  |             0 |
| 2009 | MARZO | LUNES | 2009-03-02 | 09:00:00  |             0 |

Una vez más muchas gracias, y por supuesto si tienes sugerencias para mejorarla, por favor no dudes en decirlo.

Saludos

huesos52 · #17 (**permalink**) 22/07/2009, 13:22

Tu debes entender muy bien tu problema y me alegra que hayas encontrado una solución.

Te recomiendo almacenar esta consulta en una vista para ser fácilmente manipulada.

Un saludo.