restar las horas de un datetime en mysql

[maialenlopez]

Hola,

Estoy haciendo este select:

Código MySQL:

Select inicio, fin from cdp_operaciones where noperario = 466 and inicio like '2013-04%' and fin like '2013-04%'
 

este me visualiza lo siguiente:

Código resultado select:

INICIO                          FIN
2013-04-08 22:00:00     2013-04-09 06:00:00
2013-04-09 22:00:00     2013-04-10 02:00:00
2013-04-10 01:59:00     2013-04-10 05:34:28
2013-04-10 05:30:00     2013-04-10 06:00:00
 

Necesito calcular las horas totales trabajadas, es decir, hacer "FIN-INICIO" para saber las horas trabajadas.

Como se puede observar, por ejemplo el día 8 se empezó a trabajar a las 22horas y se salio el día 9 a las 6 horas, pero el día 9 se volvió a entrar a las 22horas hasta que salio al día 10 a las 6 horas.

Como puedo hacerlo. Me estoy volviendo loca y no consigo nada

[gnzsoloyo]

Primer consejo básico: Nunca uses LIKE con valores que no sean cadenas de texto. No sólo es un error conceptual, sino que además puede tener resultados inesperados.

Código MySQL:

SELECT 
    inicio, fin, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, fin, inicio)) HORAS
FROM cdp_operaciones 
WHERE noperario = 466 
    AND YEAR(inicio) = 2013 AND  MONTH(inicio) = 4
    AND YEAR(fin) = 2013 AND  MONTH(fin) = 4;
 

[maialenlopez]

Iniciado por gnzsoloyo

Primer consejo básico: Nunca uses LIKE con valores que no sean cadenas de texto. No sólo es un error conceptual, sino que además puede tener resultados inesperados.

Código MySQL:

SELECT 
    inicio, fin, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, fin, inicio)) HORAS
FROM cdp_operaciones 
WHERE noperario = 466 
    AND YEAR(inicio) = 2013 AND  MONTH(inicio) = 4
    AND YEAR(fin) = 2013 AND  MONTH(fin) = 4;
 

Me aparece un error al ejecutar esa query. SQL Error(1064): You have an error in your SQL syntax....

[gnzsoloyo]

Código MySQL:

SELECT 
    inicio, 
    fin, 
    SEC_TO_TIME(TIMESTAMPDIFF(SECOND, fin, inicio)) HORAS
  FROM cdp_operaciones
 WHERE noperario = 466
   AND YEAR(inicio) = 2013
   AND MONTH(inicio) = 4
   AND YEAR(fin) = 2013
   AND MONTH(fin) = 4;
 

No contiene errores sintácticos.

Explica cómo la estás ejecutando y con qué datos.

[maialenlopez]

Iniciado por gnzsoloyo

Código MySQL:

SELECT 
    inicio, 
    fin, 
    SEC_TO_TIME(TIMESTAMPDIFF(SECOND, fin, inicio)) HORAS
  FROM cdp_operaciones
 WHERE noperario = 466
   AND YEAR(inicio) = 2013
   AND MONTH(inicio) = 4
   AND YEAR(fin) = 2013
   AND MONTH(fin) = 4;
 

No contiene errores sintácticos.

Explica cómo la estás ejecutando y con qué datos.

Lo estoy ejecutando con el HeidiSQL y los datos los que estan introducidos en la select, es decir, los mismos datos que has puesto tu, cojo, copio, ejecuto y me lanza ese error

[gnzsoloyo]

Mira, los datos los tomo de tu primer ejemplo, y la tabla de datos no la tengo yo, sino tu, por lo que hay dos posibilidades:
1) Estás copiando incorrectamente o poniéndole mal algo.
2) el HeidiSQL le está poniendo algo indebido (no lo puedo verificar, porque no lo uso nunca).
Vamos a lo simple: Una vez que hayas puesto los datos en la ventana de ejecución del Heidi, copia lo que tu pusiste allí y postealo acá.
Veremos exactamente qué es lo que está recibiendo tu servidor de MYSQL...

[maialenlopez]

Iniciado por gnzsoloyo

Mira, los datos los tomo de tu primer ejemplo, y la tabla de datos no la tengo yo, sino tu, por lo que hay dos posibilidades:
1) Estás copiando incorrectamente o poniéndole mal algo.
2) el HeidiSQL le está poniendo algo indebido (no lo puedo verificar, porque no lo uso nunca).
Vamos a lo simple: Una vez que hayas puesto los datos en la ventana de ejecución del Heidi, copia lo que tu pusiste allí y postealo acá.
Veremos exactamente qué es lo que está recibiendo tu servidor de MYSQL...

Pongo lo que escribo el el HeidiSQL:

Código MySQL:

SELECT inicio, fin, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, fin, inicio)) HORAS
FROM cdp_operaciones
WHERE noperario = 466
AND YEAR(inicio) = 2013
AND MONTH(inicio) = 04
AND YEAR(fin) = 2013
AND MONTH(fin) = 04
 

[gnzsoloyo]

Por lo pronto, quítale el cero a la izquierda al numero de mes. MONTH() devuelve el valor numérico del mes, y podría estar haciendo que HeidiSQL lo malinterprete.

[maialenlopez]

Iniciado por gnzsoloyo

Por lo pronto, quítale el cero a la izquierda al numero de mes. MONTH() devuelve el valor numérico del mes, y podría estar haciendo que HeidiSQL lo malinterprete.

Si le quito el 0 del 04 me sale el mismo error.

[gnzsoloyo]

Mira, para hacertelo simple, esta consulta no tiene errores.
Te lo muestro con una tabla mía, donde hay dos campos de entrada/salida (desde y hasta). Quito el noperario", porque no lo tengo en esa tabla, pero a los efectos de esta prueba, sirve:

Código MySQL:

mysql> SELECT desde, hasta, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, desde, hasta)) HORAS
    -> FROM `conduccion`
    -> WHERE
    ->     YEAR(desde) = 2011
    ->     AND MONTH(desde) = 04
    ->     AND YEAR(hasta) = 2011
    ->     AND MONTH(hasta) = 04
    -> LIMIT 10;
+---------------------+---------------------+----------+
| desde               | hasta               | HORAS    |
+---------------------+---------------------+----------+
| 2011-04-30 15:51:25 | 2011-04-30 15:51:45 | 00:00:20 |
| 2011-04-30 15:54:05 | 2011-04-30 15:54:05 | 00:00:00 |
| 2011-04-30 16:02:09 | 2011-04-30 16:02:19 | 00:00:10 |
| 2011-04-30 15:54:15 | 2011-04-30 15:57:05 | 00:02:50 |
| 2011-04-30 15:58:25 | 2011-04-30 15:58:35 | 00:00:10 |
| 2011-04-30 15:59:05 | 2011-04-30 15:59:05 | 00:00:00 |
| 2011-04-30 16:13:33 | 2011-04-30 16:16:13 | 00:02:40 |
| 2011-04-30 16:16:43 | 2011-04-30 16:17:03 | 00:00:20 |
| 2011-04-30 17:45:35 | 2011-04-30 17:45:35 | 00:00:00 |
| 2011-04-30 17:45:55 | 2011-04-30 17:46:25 | 00:00:30 |
+---------------------+---------------------+----------+
10 rows in set (0.08 sec)
 

Como puedes ver, funciona, no hay errores sintácticos, así que el problema lo debe estar generando el HeidiSQL, y no la consulta, y allí no puedo ayudarte porque no lo uso.

[maialenlopez]

Iniciado por gnzsoloyo

Mira, para hacertelo simple, esta consulta no tiene errores.
Te lo muestro con una tabla mía, donde hay dos campos de entrada/salida (desde y hasta). Quito el noperario", porque no lo tengo en esa tabla, pero a los efectos de esta prueba, sirve:

Código MySQL:

mysql> SELECT desde, hasta, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, desde, hasta)) HORAS
    -> FROM `conduccion`
    -> WHERE
    ->     YEAR(desde) = 2011
    ->     AND MONTH(desde) = 04
    ->     AND YEAR(hasta) = 2011
    ->     AND MONTH(hasta) = 04
    -> LIMIT 10;
+---------------------+---------------------+----------+
| desde               | hasta               | HORAS    |
+---------------------+---------------------+----------+
| 2011-04-30 15:51:25 | 2011-04-30 15:51:45 | 00:00:20 |
| 2011-04-30 15:54:05 | 2011-04-30 15:54:05 | 00:00:00 |
| 2011-04-30 16:02:09 | 2011-04-30 16:02:19 | 00:00:10 |
| 2011-04-30 15:54:15 | 2011-04-30 15:57:05 | 00:02:50 |
| 2011-04-30 15:58:25 | 2011-04-30 15:58:35 | 00:00:10 |
| 2011-04-30 15:59:05 | 2011-04-30 15:59:05 | 00:00:00 |
| 2011-04-30 16:13:33 | 2011-04-30 16:16:13 | 00:02:40 |
| 2011-04-30 16:16:43 | 2011-04-30 16:17:03 | 00:00:20 |
| 2011-04-30 17:45:35 | 2011-04-30 17:45:35 | 00:00:00 |
| 2011-04-30 17:45:55 | 2011-04-30 17:46:25 | 00:00:30 |
+---------------------+---------------------+----------+
10 rows in set (0.08 sec)
 

Como puedes ver, funciona, no hay errores sintácticos, así que el problema lo debe estar generando el HeidiSQL, y no la consulta, y allí no puedo ayudarte porque no lo uso.

Ahora me va perfecto, lo he echo en el programa que estoy haciendo y va perfecto como tu has puesto asi que deduzco que es el HeidiSQL que no me deja hacerlo.

Pero tengo un problema, a ver si me podrías ayudar. Imaginate esta tabla:
HIGHLIGHT="MySQL"]mysql>
+---------------------+---------------------+
| desde | hasta |
+---------------------+---------------------+
| 2011-04-09 22:00:25 | 2011-04-10 02:00:00 |
| 2011-04-10 02:05:00 | 2011-04-10 06:00:00 |
| 2011-04-10 22:00:25 | 2011-04-11 02:30:00 |
| 2011-04-11 02:35:00 | 2011-04-11 06:00:00 |
+---------------------+---------------------+----------+
10 rows in set (0.08 sec)[/HIGHLIGHT]

Son dos relevos de noche de una persona en el mes de abril. como harias para calcular las horas transcurridas?

Un saludo y gracias

[gnzsoloyo]

Ese mismo problema lo he tenido antes, y lo tengo resuelto en un proyecto en el que ya no estoy trabajando, pero del que aún tengo todos los fuentes y documentación. Si me esperas a esta noche, te lo paso.
De todos modos, la clave es mas o menos simple: Se debe hacer un calculo segmentado entre el inicio y las 23:59:59 de cada día, y luego otro entre las 00:00:00 y el final, para leugo hacer la suma resultante de ambos.
Sintácticamente queda un poco complicada, pero no es difícil de escribir. En la empresa en que ahora trabajo no tengo MYSQL como para bosquejarlo, por lo que te pido que me esperes.

[maialenlopez]

Iniciado por gnzsoloyo

Ese mismo problema lo he tenido antes, y lo tengo resuelto en un proyecto en el que ya no estoy trabajando, pero del que aún tengo todos los fuentes y documentación. Si me esperas a esta noche, te lo paso.
De todos modos, la clave es mas o menos simple: Se debe hacer un calculo segmentado entre el inicio y las 23:59:59 de cada día, y luego otro entre las 00:00:00 y el final, para leugo hacer la suma resultante de ambos.
Sintácticamente queda un poco complicada, pero no es difícil de escribir. En la empresa en que ahora trabajo no tengo MYSQL como para bosquejarlo, por lo que te pido que me esperes.

Ok yo te espero. Voy a intentar a ver si me sale algo con lo que me has dicho, pero te espero a la noche. Estas siendo de gran ayuda.

Gracias

[gnzsoloyo]

Bueno, al final, luego de revisar el SP que había construido para este fin, me encontré que es demasiado complejo para una prueba inicial. La verdad, es para expertos en MySQL (terriblemente complejo, con tablas temporales, tablas de hechos, uso de variables de sesión, parametrizaciones varias, control de excepciones... un asco. Estaba hecho para un sistema empresario).
Como sea, tomé la lógica básica y te armé este modelo, que tal vez se entienda:

Código MySQL:

SELECT T1.fecha, SEC_TO_TIME(T1.horasinicio + T2.horasinicio) totalHorasdía
FROM
    (SELECT 
        DATE(hasta) fecha,
        TIMESTAMPDIFF(SECOND, DATE_ADD(DATE(desde), INTERVAL 86399 second), hasta) horasfin
    FROM tabla
    WHERE
        YEAR(desde) = 2011
        AND MONTH(desde) = 04
        AND YEAR(hasta) = 2011
        AND MONTH(hasta) = 04
        AND TIME(DESDE) > TIME(HASTA)) T1
    INNER JOIN 
    (SELECT 
        DATE(desde) fecha,
        TIMESTAMPDIFF(SECOND, desde, DATE_ADD(DATE(desde), INTERVAL 86399 second)) horasinicio
    FROM tabla
    WHERE
        YEAR(desde) = 2011
        AND MONTH(desde) = 04
        AND YEAR(hasta) = 2011
        AND MONTH(hasta) = 04
        AND TIME(hasta) > TIME(desde) 
        AND DATE(desde) = DATE_SUB(hasta, INTERVAL 1 DAY)) T2 ON T1.fecha = T2.fecha;
 

La idea es que se hacen dos subconsultas, cada una para obtener una mitad del día: Las horas desde cierta hora hasta medianoche, y las que corresponden a las cero horas hasta la mañana.
Cada subconsulta construye una tabla derivada (en el FROM), las cuales a su vez se emparejan con un JOIN, devolviendo un único por día. As su vez, la subconsulta sólo obtiene los segundos y luego se suman en la consulta mayor, para convertirlos en horas.
Para que te sea funcional hay que agregar a cada subconsulta el ID del cliente, empelado o usuario buscado, y agregar ese mismo datos como parte del ON del JOIN.
Prueba bosquejar tu consulta sobre este esquema y posteala. Veremos si se ha entendido, antes de probarla.

[maialenlopez]

Iniciado por gnzsoloyo

Bueno, al final, luego de revisar el SP que había construido para este fin, me encontré que es demasiado complejo para una prueba inicial. La verdad, es para expertos en MySQL (terriblemente complejo, con tablas temporales, tablas de hechos, uso de variables de sesión, parametrizaciones varias, control de excepciones... un asco. Estaba hecho para un sistema empresario).
Como sea, tomé la lógica básica y te armé este modelo, que tal vez se entienda:

Código MySQL:

SELECT T1.fecha, SEC_TO_TIME(T1.horasinicio + T2.horasinicio) totalHorasdía
FROM
    (SELECT 
        DATE(hasta) fecha,
        TIMESTAMPDIFF(SECOND, DATE_ADD(DATE(desde), INTERVAL 86399 second), hasta) horasfin
    FROM tabla
    WHERE
        YEAR(desde) = 2011
        AND MONTH(desde) = 04
        AND YEAR(hasta) = 2011
        AND MONTH(hasta) = 04
        AND TIME(DESDE) > TIME(HASTA)) T1
    INNER JOIN 
    (SELECT 
        DATE(desde) fecha,
        TIMESTAMPDIFF(SECOND, desde, DATE_ADD(DATE(desde), INTERVAL 86399 second)) horasinicio
    FROM tabla
    WHERE
        YEAR(desde) = 2011
        AND MONTH(desde) = 04
        AND YEAR(hasta) = 2011
        AND MONTH(hasta) = 04
        AND TIME(hasta) > TIME(desde) 
        AND DATE(desde) = DATE_SUB(hasta, INTERVAL 1 DAY)) T2 ON T1.fecha = T2.fecha;
 

La idea es que se hacen dos subconsultas, cada una para obtener una mitad del día: Las horas desde cierta hora hasta medianoche, y las que corresponden a las cero horas hasta la mañana.
Cada subconsulta construye una tabla derivada (en el FROM), las cuales a su vez se emparejan con un JOIN, devolviendo un único por día. As su vez, la subconsulta sólo obtiene los segundos y luego se suman en la consulta mayor, para convertirlos en horas.
Para que te sea funcional hay que agregar a cada subconsulta el ID del cliente, empelado o usuario buscado, y agregar ese mismo datos como parte del ON del JOIN.
Prueba bosquejar tu consulta sobre este esquema y posteala. Veremos si se ha entendido, antes de probarla.

Seria algo como esto?

Código MySQL:

SELECT T1.fecha, SEC_TO_TIME(T1.horasinicio + T2.horasinicio) totalHorasdía
FROM
    (SELECT 
        DATE(hasta) fecha,
        TIMESTAMPDIFF(SECOND, DATE_ADD(DATE(desde), INTERVAL 86399 SECOND), hasta) horasfin
    FROM tabla
    WHERE
        noperario = 466
        AND YEAR(desde) = 2011
        AND MONTH(desde) = 04
        AND YEAR(hasta) = 2011
        AND MONTH(hasta) = 04
        AND TIME(DESDE) > TIME(HASTA)) T1
    INNER JOIN 
    (SELECT 
        DATE(desde) fecha,
        TIMESTAMPDIFF(SECOND, desde, DATE_ADD(DATE(desde), INTERVAL 86399 SECOND)) horasinicio
    FROM tabla
    WHERE
        noperario = 466
        AND YEAR(desde) = 2011
        AND MONTH(desde) = 04
        AND YEAR(hasta) = 2011
        AND MONTH(hasta) = 04
        AND TIME(hasta) > TIME(desde) 
        AND DATE(desde) = DATE_SUB(hasta, INTERVAL 1 DAY)) T2 ON T1.fecha = T2.fecha AND T1.noperario = T2.noperario;