Consulta MySQL con fechas epoch (unix) y group by

Citar

Escrito el 15 Ago 2013 02:23 pm

Hola gente.

Estoy creando una aplicación web que funcione paralelamente a un foro que llevo. En ella, una de las cosas que estoy haciendo es introducir un sistema de estadísticas propio que trabajará con chartjs. Una de las cosas primordiales es extraer la información necesaria por lo que tengo que atacar a consultas a la base de datos.

Mi experiencia con MySQL no es muy alta así que me encuentro con algunos interrogante como es este de las fechas epoch que me estan dificultando algo las consultas.

Mi intención es la siguiente. Quiero extraer de la base de datos todos los posts creados en el foro desde la fecha actual hasta 14 dias atrás (aunque luego eso podría ser parametrizado para que cada usuario elija el intervalo que desee... para no complicarme de momento, en principio ese intervalo) y luego agrupar todos los posts de cada dia para determinar cuantos posts se introdujeron cada dia.

Pues bien después de muchas horas de darle vueltas conseguí que funcionase la consulta "mas o menos" y es que esto de las fechas me está matando... os muestro el código que he usado para conseguir lo que tengo:

Código PHP :

$f_fin= time(); //Aquí la fecha actual, que php me devuelve en formato de fecha epoch
$f_inicio= time()-(14 * 24 * 60 * 60);//Aquí la fecha final del intervalo deseado, que como podéis ver, resta un numero concreto de segundos. No creo que haga falta explicar esas multiplicaciones, es lo mas legible a la hora de revisar cuanto intervalo estoy usando.

... y con esas 2 variables definidas, ahora voy al codigo del MySQL

Código MySQL :

SELECT DATE_FORMAT(FROM_UNIXTIME( post_time),'%y %m %d') as post_time2,post_time, COUNT(post_time) FROM tabla_posts WHERE post_time BETWEEN '".$f_inicio."' AND '".$f_fin."' GROUP BY post_time2 ORDER BY post_time DESC

Esto me devuelve en principio lo que quiero pero con una particularidad no deseada, al trabajar la tabla (y en concreto el campo de fecha) con fechas epoch (en segundos), la fórmula lo que hace es calcular desde la fecha exácta de la ejecución de la consulta (ajustada al segundo) un intervalo que va desde ese preciso instante hasta 1.209.600 segundos atrás ¿cual es el resultado?
El resultado es que en la fecha de HOY me muestra las entradas de 'HOY' contando desde este preciso instante hacia atrás 24 horas (lo cual implica que recoge valores del dia de ayer y los cuenta como de hoy) y así hasta el final de intervalo.

Explicado de otra forma, no tiene encuenta cuando empieza o acaba 'oficialmente' un dia, el 'corte' lo hace, digamos, por saltos de 24 horas hacia atrás. Lo que yo quiero (y creo que es lo correcto) es que cuente las entradas de cada dia teniendo en cuenta el 'cambio oficial/natural' de fecha, recogiendo también, del dia del final de intervalo (a fecha de este post sería el 2 de agosto), no solo los posts que hay dentro del intervalo especificado (al segundo) sino que todos los posts naturales de ese dia).

He de decir, para quien quiera ayudarme que empecé a usar una mezcolanza de instrucciones de MySQL para convertir todas las fechas de la consulta a un formato '%Y-%m-%d' con lo que poder solventar el asunto, tales como FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d'), pero en mis intentonas no he conseguido que devuelva siquiera resultados y no sé qué estoy haciendo mal.

*Aquí tenéis un ejemplo de lo que digo en mi último párrafo pero aplicado a los posts escritos por un usuario en concreto en los últimos 14 dias y de cuya consulta no consigo extraer datos... pero que es donde intento pre-formatear las fechas primero para que los intervalos no estén conteadas al segundo:

Código MySQL :

SELECT DATE_FORMAT(FROM_UNIXTIME( post_time),'%y %m %d') as post_time2, poster_id, post_time, COUNT(poster_id) FROM tabla_posts WHERE poster_id='136393' AND FROM_UNIXTIME(post_time, '%Y-%m-%d') BETWEEN FROM_UNIXTIME(DATE(), '%Y-%m-%d') AND FROM_UNIXTIME(DATE()-14, '%Y-%m-%d') GROUP BY post_time2,poster_id ORDER BY post_time DESC

¿alguien puede ayudarme?

Por pitger

Claber

201 de clabLevel

Genero:Masculino

Envíale un mensaje privado Email Web

Cáceres

Citar

Escrito el 15 Ago 2013 05:21 pm

Creo que lo que quieres, lo puedes hacer así:

Código PHP :

$f_inicio = strtotime(Date("Y-m-d")." -14 days");

Eso te devolverá el tiempo epoc desde las 0 horas, 0 minutos y 0 segundos de hace 14 días atrás.

Por DriverOp

Claber

2510 de clabLevel

Envíale un mensaje privado Web

Citar

Escrito el 15 Ago 2013 08:28 pm

DriverOp escribió:

Creo que lo que quieres, lo puedes hacer así:

Código PHP :

$f_inicio = strtotime(Date("Y-m-d")." -14 days");

Eso te devolverá el tiempo epoc desde las 0 horas, 0 minutos y 0 segundos de hace 14 días atrás.

Lo intenté ya también, solo que la conversión la hice así:

Código PHP :

$f_inicio =date('Y-m-d', strtotime('-2 week')); //El resultado, comprobado con print $f_inicio me dice que es igualmente válido.

En todo caso... probando y analizando resultados.

De salida, si cambio la variable, de fecha epoch a Años-mes-dias, he de hacer lo mismo con todas las referencias de fechas pues MySQL no podría comparar fechas epoch con fechas 'cristianas'.

sería:

Código PHP :

$f_fin = strtotime(Date("Y-m-d"));
$f_inicio = strtotime(Date("Y-m-d")." -14 days");// bien esto o la que dije yo... lo he probado con ambas en todos los ejemplos que vienen a continuación

y en la consulta MySQL tengo que hacer lo mismo con el campo post_time que está guardado en epoch, concretamente cuando evalúa el "...WHERE post_time...", y es ahí donde tengo de nuevo problema porque he usado...

Código MySQL :

SELECT DATE_FORMAT(FROM_UNIXTIME( post_time),'%y %m %d') as post_time2,post_time, COUNT(post_time) FROM tabla_posts WHERE DATE_FORMAT(FROM_UNIXTIME( post_time),'%y %m %d') BETWEEN '".$f_inicio."' AND '".$f_fin."' GROUP BY post_time2 ORDER BY post_time DESC

y no va, no devuelve error pero tampoco devuelve dato alguno. Luego he intentado cambiando el '%y %m %d' por '%Y-%m-%d', con los guiones y la Y en mayúsculas, igual que en las variables de php, no fuese a ser que al no ser la conversión a algo exacto fuese a fallar...

Código MySQL :

SELECT DATE_FORMAT(FROM_UNIXTIME( post_time),'%Y-%m-%d') as post_time2,post_time, COUNT(post_time) FROM tabla_posts WHERE DATE_FORMAT(FROM_UNIXTIME( post_time),'%Y-%m-%d') BETWEEN '".$f_inicio."' AND '".$f_fin."' GROUP BY post_time2 ORDER BY post_time DESC

y tampoco funciona, sin errores, pero tampoco resultados (en blanco). Tambien he intentado con el valor ya convertido de post_time2...

Código MySQL :

SELECT DATE_FORMAT(FROM_UNIXTIME( post_time),'%y %m %d') as post_time2,post_time, COUNT(post_time) FROM tabla_posts WHERE post_time2 BETWEEN '".$f_inicio."' AND '".$f_fin."' GROUP BY post_time2 ORDER BY post_time DESC

... pero me dice "unknown column 'post_time2'". Si tengo que utilizar el valor de una la columna de fecha, que está en epoch (y he de formatearla 'al vuelo'), no se me ocurre como, lo he intentado de varias formas.

Por pitger

Claber

201 de clabLevel

Genero:Masculino

Envíale un mensaje privado Email Web

Cáceres

Citar

Escrito el 15 Ago 2013 08:38 pm

DriverOp escribió:

Creo que lo que quieres, lo puedes hacer así:

Código PHP :

$f_inicio = strtotime(Date("Y-m-d")." -14 days");

Eso te devolverá el tiempo epoc desde las 0 horas, 0 minutos y 0 segundos de hace 14 días atrás.

Por cierto, siendo certeros y por no dar equivoco a nadie, tu ejemplo no devuelve el tiempo epoch equivalente a las 00:00:00 horas de hace 14 dias... devuelve "2013-08-01" que no es fecha epoch, es una fecha formateada a fechas "cristianas".

Yo he dado por supuesto que querías decir que al convertir la fecha a "cristiano", si hago con todas las fechas igual, podré obtener resultados por dias naturales. Lo malo es conseguir que al evaluar en el MySQL la fecha de cada post del foro (que están guardadas en epoch), poder convertir esas fechas 'al vuelo' a "cristiano", para inmediatamente ver si está en el rango que hemos definido con las variables.

Por pitger

Claber

201 de clabLevel

Genero:Masculino

Envíale un mensaje privado Email Web

Cáceres

Citar

Escrito el 16 Ago 2013 01:56 am

Pues lamento contradecirte y a las pruebas me remito.

El código que he puesto sí devuelve una fecha epoch y es la fecha hora correcta según lo has pedido:

Código PHP :

<?PHP
$f_inicio = strtotime(Date("Y-m-d")." -14 days");
echo $f_inicio."<br />"; // 1375394400
echo Date("Y-m-d H:i:s",$f_inicio); // 2013-08-02 00:00:00
?>

Lo que no entiendo es por qué insistes en tratar de convertir los campos timestamp a date en la cláusula WHERE de la sentencia SQL cuando todo lo que tienes que hacer es

Código MySQL :

SELECT * FROM `tabla_posts` WHERE `post_time` >= ".$f_inicio." ORDER BY `post_time`

No necesitas establecer un rango ("BETWEEN") porque todos los post cuya fecha epoch es mayor a $f_inicio son hasta hoy.

Luego de obtener los registros que te interesan puedes hacer la conversión de epoch a fecha.

El tipo timestamp no es más que un número entero que es más cómodo de tratar que si fuera un tipo fecha estanda (DATE o DATETIME) así que puedes hacer comparaciones directamente.

El error que comentas se debe a que no puedes usar alias de campos en la cláusula WHERE porque esto se analiza antes de crear los alias.

Por DriverOp

Claber

2510 de clabLevel

Envíale un mensaje privado Web

Citar

Escrito el 16 Ago 2013 03:37 am

Respecto a lo primero... he de darte la razón :oops:

. Metí eso de mala manera y como toda la consulta me seguía dando fallo, interpreté que venía de esta misma porción.

No acabo de entender, me quuedo un poco :wtf:

de como montaste eso: $f_inicio = strtotime(Date("Y-m-d")." -14 days");
... es decir, la parte de: ." -14 days" Ni siquiera en la página de php.net encuentro un ejemplo similar y no acabo de entender la lógica del ".", pero funciona!.

Antes de volver por aquí a buscar alguna posible respuesta, estuve dando muchas vueltas e incluso he de decir que había conseguido lo que me proponía, pero de otra forma un tanto mas complicada, declarando mas variables con las que restar las horas:minutos:segundos que me sobraban e hice esto:

Código PHP :

$f_inicio= time()-(14 * 24 * 60 * 60)-(($hora*3600)+($minuto*60)+$segundo);

... pero está claro que tu forma es bastante mas sencilla y limpia... y ahora que me fijo, creo que otro problema, es que desde un principio estaba usando time() (el cual captura hasta los segundos) en vez de date() y al restar 14 dias, también tendría en cuenta hora:minutos:segundos.

En cuanto a la consulta MySQL, tienes tu parte de razón y me gusta que me hayas comentado eso de evitar el BETWEEN porque ni se me había ocurrido (ya ves, con lo facil que era eso así)... pero claro, yo estaba en todo momento pensando en que a posteriori, el usuario deberá poder elegir los intervalos de consulta y en ese caso el intervalo podría ser del 1/1/13 al 1/6/13 y ahí si que ni me planteé que sobrase la variable $f_final por lo que creo que es inevitable el BETWEEN.

Te cuento... insistía y trataba de convertir el campo 'post_time' a fecha convencional porque como no era capaz de conseguir la fecha epoch correcta en el parámetro "DESDE", creí que en realidad lograr la fecha epoch correcta sería mas complicado que pasar todo primero a 'fecha convencional' y evaluar en función de dias convencionales donde quizá no se tendría en cuenta las horas, minutos y segundos. ( y al final ya has visto el pedazo de churro que monté en $f_inicial para lograrlo!)

Respecto a los alias, comprendo lo que dices pero me parece raro pues en los propios ejemplos que he visto los usan despues en el GROUP BY... (supongo que es por tanto en la parte de la consulta MySQL que evalúa, no en la que finalmente ordena y agrupa los resultados).

Gracias por tus respuestas, voy a acomodar la parte php que veo bastante mas limpia y me ahorro 3 variables extra.

Por pitger

Claber

201 de clabLevel

Genero:Masculino

Envíale un mensaje privado Email Web

Cáceres

Citar

Escrito el 16 Ago 2013 04:02 am

Dirás que igual es abusar... pero me acabo de dar cuenta de otro problema que sale de aquí.

Veo que hay dias en los que no hay posts y en vez de mostrar 0 entradas, se salta el registro y se va al siguiente... el efecto es que hay dias que salta y no aparecen. No sé si eso se corrige en el MySQL o en el While de php

Código MySQL :

SELECT DATE_FORMAT(FROM_UNIXTIME(post_time),'%y %m %d') as post_time2,post_time, COUNT(post_time) FROM cnvrt_posts WHERE post_time BETWEEN '".$f_inicio."' AND '".$f_fin."' GROUP BY post_time2 ORDER BY post_time DESC

y la parte while donde imprimo los datos (con un sencillo uso de divs para emular de momento graficas hasta que me meta con chart.js)

Código PHP :

while( $result = mysql_fetch_array($MQuery1) ){?>
<div style="float:left; margin-left:3px; margin-top:3px;text-align:center;width:99%; height:30px; ">
    <div style="float:left; margin:2px; vertical-align:middle; text-align:center;height:20px;width:200px;background-color:#0099CC">
        <span class="textoB"><?php echo date("d/M/Y",$result['post_time']); ?></span> .::.   
        <span class="textoW">Entradas:<?php echo $result['COUNT(poster_id)']; ?></span>   
    </div>
    <div style="float:left;margin:2px; text-align:center;height:20px;width:<?php echo $result['COUNT(poster_id)']*5; ?>px;background-color:#00cc00">  
    </div>
</div>
<?php } //fin del loop while

Por pitger

Claber

201 de clabLevel

Genero:Masculino

Envíale un mensaje privado Email Web

Cáceres

Citar

Escrito el 16 Ago 2013 12:29 pm

Bueno, has traído varias cuestiones.

La documentación que estabas buscando respecto de strtotime() es esta:
http://ar2.php.net/manual/es/datetime.formats.relative.php

Para consultar por un rango de fechas usando epoch, como ya mencioné en mi mensaje anterior, no es más que comparar un entero:

Código MySQL :

SELECT * FROM `cnvrt_posts`
WHERE `post_time` <= $f_inicio AND `post_time` >= $f_fin

Siempre y cuando hagas la conversión de las fechas ingresadas por el usuario a epoch, claro está. Y como ves no necesitas BETWEEN.

En la cláusula GROUP BY sí puedes usar alias de campo porque es casi lo último que se evalúa, antes de ORDER BY y después de SELECT.

Respecto de los días "faltantes". Pues MySQL no tiene por qué mostrarte lo que no hay, o sea, si no hay registros con una fecha dada, ¿por qué tendría que decirte que ese día hay cero registros?.

Pero entiendo tu problema, quieres llenar los que faltan. Para ello, solo usando MySQL implica crear una tabla temporal con todas las fechas en el rango deseado y luego hacer un JOIN con la tabla en cuestión para finalmente hacer el GROUP BY con COUNT() tal como lo haces ahora. Así el GROUP BY agrupará por fechas y las fechas faltantes en la tabla pero que sí están en la tabla de fechas se completarán con NULL.

Bueno, más o menos es eso.

Por DriverOp

Claber

2510 de clabLevel

Envíale un mensaje privado Web

Citar

Escrito el 16 Ago 2013 01:42 pm

Nuevamente evitas el BETWEEN.... que crack!!! :lol:

Supongo que a nivel de efectividad (tiempo de procesado y tal) ambas formas vienen a ser lo mismo aunque siempre viene bien recordar que existe esa posibilidad de hacer uso de OPERADORES.

Es cierto, siendo estrictos, en ningun momento se le dice a MySQL que invente datos que no existen; es una consulta literal de los datos QUE HAY, y lo que no hay, evidentemente no se lo puede inventar ni interpretar correctamente.

Revisando la documentación sobre NULL en el dev.mysql explican que puede existir la diferencia entre NULL y "" (en blanco) y usan ejemplo de un campo frecuente en una tabla, el teléfono, donde NULL significa que no está recogido ese dato (porque aun no se tiene) mientras que "" (espacio en blanco) significa que se sabe que ese cliente NO tiene teléfono.

En mi ejemplo, que recojo la inserción de posts y filtrando además el campo "poster_id" puedo determinar las entradas de un usuario en concreto, y el que haya ausencia de datos algunos dias NO es necesariamente ausencia de información, habrá de indicar que ese usuario, ese dia, no escribió nada (con un CERO).

La solución que me das, tengo que estudiarla (y ver cuan dificil es cada uno de los pasos) Y no es que quiera ceñirme por capricho al uso exclusivo de las consultas MySQL. Yo lo que busco es la solución mas efectiva, la que menos tiempo y carga de procesado y memoria lleve. No sé trabajar a un alto nivel (considero que ni a medio siquiera) con MySQL o PHP pero si tengo claro la importancia de tratar de hacerlo de la forma mas eficiente.

Si en este ejemplo mio me dices... "oye Pitger, lo mas eficiente ahí es que previamente a la consulta y justo después a la declaración de las variables que recogen el intervalo de fechas, crees un array con el listado de fechas que se van a evaluar en la CONSULTA y lo utilices como campo de salida de datos donde se le van anexionando los COUNT(post_time) y sustituyendo los valores nulos (null) por ceros donde corresponda en la cadena de salida".

También me puedes decir: "Pitger, no!!, lo mas eficiente para el server y para lo que necesitas es lo que te he dicho en mi post anterior... una tabla temporal y con JOIN vas escapando los resultados sobre esa tabla segun corresponda y haciendo lo propio para las fechas con NULL= 0 posts".

(Ya te anticipo que solo pensar en ese paso, ahora y antes de ponerme manos a la obra, me veo un poco perdido... pero ahí está el reto del escalador: "¿por donde empiezo a subir esta montaña?")

Por pitger

Claber

201 de clabLevel

Genero:Masculino

Envíale un mensaje privado Email Web

Cáceres

Citar

Escrito el 16 Ago 2013 04:12 pm

"la importancia de tratar de hacerlo de la forma mas eficiente."
Me gusta tu actitud

.

Pues siguiendo esa máxima es mejor el array de PHP en vez de una tabla temporal en MySQL simplemente porque es más rápido y consume menos recursos.

Y no uses BETWEEN si puedes evitarlo y en este caso puedes evitarlo...

Por DriverOp

Claber

2510 de clabLevel

Envíale un mensaje privado Web

Consulta MySQL con fechas epoch (unix) y group by

Escribe una respuesta rapida