Ordenar registros por fecha

[filmixt]

Hola a todos,
Tengo un sistema de estadisticas con los siguientes datos:

id refer ip date hour

date es d/m/Y (01/01/2008)
hour va por G:i (18:50)

El objetivo sería mostrar las visitas de los ultimos 30 dias (por ejemplo) PERO añadiendo +6 horas debido a que el servidor es de EEUU, tiene deshabilitado el modulo que permite cambiar la hora por .htaccess y además no se puede cambiar la hora con putenv de php =D).

Así que lo tengo algo crudo.
Ahora mismo tengo un codigo bastante bueno, pero como mi cabeza con tantisimas ecuaciones da vueltas, a ver si podéis añadir por mysql +6 horas a la hora, ordenarlo en descending (las entradas mas recientes antes) y claro, separado por dias:

dia 20/10/08 - X visitas
...

A ver si hay por aqui un artista de los buenos para maravillarnos con su sabiduria

Saludos y gracias de antemano!

[jurena]

Seguramente podrá hacerse algo más fácil, pero por el momento prueba esto:

Código sql:

SELECT DATE( ADDTIME( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) , `hour` ) ) AS DIA, COUNT( * ) AS TOTAL, DATE_ADD( NOW( ) , INTERVAL 6 HOUR ) AS Fechorareferencia, NOW( ) AS Fechahoraservidor
FROM nombretabla
WHERE ADDTIME( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) , `hour` ) > DATE_ADD( DATE_ADD( NOW( ) , INTERVAL 6 HOUR ) , INTERVAL -31
DAY ) 
GROUP BY DATE( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) ) 
ORDER BY DATE( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) ) DESC
 

Ten cuidado con los nombres de los campos. Date y hour son palabras reservadas y no deberías utilizarlas para nombrar campos. Observa que las he tenido que poner entre acentos graves para evitar problemas. Te he añadido la hora del servidor y la de referencia para que veas que la toma, pero nada de eso será necesario mostrarlo. Te bastará con esto

Código sql:

SELECT DATE( ADDTIME( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) , `hour` ) ) AS DIA, COUNT( * ) AS TOTAL
FROM nombretabla
WHERE ADDTIME( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) , `hour` ) > DATE_ADD( DATE_ADD( NOW( ) , INTERVAL 6 HOUR ) , INTERVAL -31
DAY ) 
GROUP BY DATE( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) ) 
ORDER BY DATE( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) ) DESC
 

[filmixt]

Hola,
He probado el codigo, muchas gracias.
Pero la verdad es que toco muy poco mysql, la uso como contenedor de datos pero no trato mucho con ellos.

Tu codigo me da una lista de los dias que hay con esos records, pero... necesito mostrar las visitas con éstos, ¿como se mostraria?

Esto es lo que tengo yo:

Código:

<?php
include("config.php");
$k = mysql_query("SELECT DATE( ADDTIME( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) , `hour` ) ) AS DIA, COUNT( * ) AS TOTAL
FROM views
WHERE ADDTIME( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) , `hour` ) > DATE_ADD( DATE_ADD( NOW( ) , INTERVAL 6 HOUR ) , INTERVAL -31
DAY ) 
GROUP BY DATE( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) ) 
ORDER BY DATE( STR_TO_DATE( `date`, '%d/%m/%Y %H:%i:%s' ) ) DESC");
while($datos = mysql_fetch_array($k)){
echo "a: ".$datos[0]."<br />";
}
?>

Saludos

[jurena]

No sé si te he entendido bien. Dime qué es exactamente lo que quieres con un ejemplo. Observo que escribes código PHP en este foro. No debes hacerlo. Prueba la consulta que te puse directamente en consola o mediante la pantalla sql del PHPMyAdmin y observa los resultados. Si todo está bien, luego cargarás mediante PHP y para eso debes vigilar que estás usando la sintaxis correcta, y creo que no es así. Pide que te revisen en el foro PHP la sintaxis empleada para mostrar los datos...

[filmixt]

Ya se mostrarlas, gracias.

Pero hay algo que me parece RARISIMO señores.
Tengo un codigo que hace lo mismo pero por PHP y que obviamente es mas largo xD, aqui os lo dejo:

Código:

<?php
$l = 0;
// Bucle de dias, mostrar 31 ultimso dias
while($l < 31){
$no = $l+1;
// Muestro simplemente 1. Fecha, 2. Fecha...
if($no < 10){ echo 0; } echo $no.".";
// Muestro la fecha a tratar                    
echo date("d/m/Y", mktime(date("G")+6, date("i"), 0,  date("m"), date("d")-$l, date("Y")));
// Conexion DB
include("config.php");
// Selecciono tabla
$kk = mysql_query("SELECT * from views order by id desc");
// Me remonto a hoy y AÑADO 6 horas para estar en horario español
$hoy = date("d/m/Y", mktime(date("G")+6, date("i"), 0,  date("m"), date("d")-$l, date("Y")));
// Recojo los registros del dia
$parahoy = 0;
// Bucle
while($datos = mysql_fetch_array($kk)){
// Divido fecha
$date = explode("/", $datos[date]);
$dia = $date[0];
$mes = $date[1];
$ano = $date[2];
// Divido hora
$hour = explode(":", $datos[hour]);
$hora = $hour[0];
$mins = $hour[1];
// Dia y hora de los REGISTROS recogidos
$hora2 = date("d/m/Y", mktime("$hora"+6, "$mins", 0,  "$mes", "$dia", "$ano"));
// Si el dia coincide con el del bucle, añadir a $parahoy
if($hoy==$hora2){
$parahoy++;
}

}
// Muestro parahoy del dia
echo $parahoy;
// Sumamos dia
$l++; 
// Volvemos a repetir
 }
  ?>

El problema es que a mi me muestran datos distintos al del codigo que me ha proporcionado el compañero.
¿Has restado en vez de sumar las 6 horas?
¿Hay algo mal? :P

Saludos y muchas gracias

[filmixt]

Acabo de fijarme, jurena, si cambio las horas de interval, las estadisticas NO VARIAN, debe de haber algo que falla en el codigo..
Ya me contarás.
Saludos.

[jurena]

Explícame un poco más cómo cargas esas fechas y lo que quieres conseguir con ejemplos que sean datos concretos. Creo que esto puede solucionarse con MySQL directamente. Lo que no debemos es incluir código PHP en este foro. Es probable que mi código esté mal, porque sólo he añadido 6 horas a la hora de referencia y no a las horas cargadas, e imagino que si se cargaron desde el mismo servidor, tendrás que sumarlas. Eso puede solucionarse, pero acláranos con un ejemplo la situación.

[filmixt]

De acuerdo mira,

Un usuario entra a mi web, y si su IP no está en la DB (hablamos de que no esté esa IP en ese dia, si está del dia anterior, se añadiria la del dia actual)

En fin, hoy entraron 300 personas por ejemplo, entonces tengo 300 entradas, con ip's distintas, con el campo date y el campo hour.
Un usuario entra el dia 20/10/08 a las 18:20 y se registra como tal en los campos correspondientes.

Lo que habria que hacer es poner al dia la fecha de AHORA, y la HORA de los registros, para que así trabajemos con datos de horarios españoles.
Una vez hecho esto, ordenariamos los datos, porque ahora mismo lo que son las visitas, el dia empezaría 6 horas antes que en la hora española, es decir, a las 5am las estadisticas se resetearian en vez de a las 12am.
Saludos.

[jurena]

Prueba esta consulta y me dices:

Código sql:

SELECT DATE( ADDDATE( ADDTIME( STR_TO_DATE( `date` , '%d/%m/%Y %H:%i:%s' ) , `hour` ) , INTERVAL 6 HOUR ) ) AS DIA, COUNT( * ) AS TOTAL
FROM tabla
WHERE ADDDATE( ADDTIME( STR_TO_DATE( `date` , '%d/%m/%Y %H:%i:%s' ) , `hour` ) , INTERVAL 6 HOUR ) > DATE_ADD( DATE_ADD( NOW( ) , INTERVAL 6 HOUR ) , INTERVAL -31
DAY ) 
GROUP BY DATE( ADDDATE( ADDTIME( STR_TO_DATE( `date` , '%d/%m/%Y %H:%i:%s' ) , `hour` ) , INTERVAL 6 HOUR ) ) 
ORDER BY DATE( ADDDATE( ADDTIME( STR_TO_DATE( `date` , '%d/%m/%Y %H:%i:%s' ) , `hour` ) , INTERVAL 6 HOUR ) ) DESC
 

[filmixt]

=D ¡gracias jurena! Te lo agradezco muchisimo!.

Un saludo, y espero poder ayudarte alguna vez en php :P

[jurena]

Me alegro de que parezca ir bien, filmixt, y te agradezco el ofrecimiento.
Pero no olvides el consejo que te di sobre el nombre de los campos: evita las palabras reservadas.