Consulta MySQL para capturar el tiempo de trabajo entre eventos -- mysql campo con date campo con hour camp Relacionados El problema

MySQL query to capture the working time between events


0
vote

problema

Español

Estoy tratando de crear una consulta que capturará las horas de trabajo tomadas para que ocurra un evento. En los datos a continuación, me gustaría mostrar las horas de trabajo tomadas para que una cuenta salga de activada a desactivada.

  ENCODEDKEY  TRANSACTIONID   LOANPRODUCTKEY  TIMESTAMP              TYPE 1           2067            aa1             2015/02/06 15:29:00    LOAN_PRODUCT_ACTIVATED 2           2162            aa1             2015/02/16 14:07:00    LOAN_PRODUCT_EDITED 3           2666            aa1             2015/02/16 15:29:00    LOAN_PRODUCT_DEACTIVATED 4           3456            aa2             2015/03/06 12:01:00    LOAN_PRODUCT_ACTIVATED 5           3478            aa2             2015/03/08 13:15:00    LOAN_PRODUCT_EDITED 6           3908            aa2             2015/03/18 13:15:00    LOAN_PRODUCT_DEACTIVATED   

Entonces, los resultados serían algo así como

  LOANPRODUCTKEY          TIME aa1                     24:00:00 aa2                     12:00:00   

(¡Sé que esos números están equivocados!)

También necesito que solo considere las horas de trabajo (I.E 9AM a 5pm) ¿es posible esto?

Gracias a cualquiera que pueda ayudarme.

actualización. gran gracias a los que han ayudado hasta ahora!

Así que he logrado crear una consulta que devolverá las fechas correctas para los tipos activados y desactivados por préstamo. Sin embargo, todavía estoy luchando para trabajar las horas de trabajo entre las dos fechas calculadas. Mi consulta es la siguiente:

  SELECT  att.LOANPRODUCTKEY ,sub1.time_activated ,sub2.time_deactivated  from  activity att  left join (select min(att.TIMESTAMP) as time_activated ,att.loanproductkey from activity att where att.`TYPE` = "LOAN_PRODUCT_ACTIVATED" group by att.LOANPRODUCTKEY) AS sub1 ON att.LOANPRODUCTKEY = sub1.LOANPRODUCTKEY  left join  (select max(att.timestamp) as time_deactivated ,att.LOANPRODUCTKEY from activity att where att.`TYPE` = "LOAN_PRODUCT_DEACTIVATED" group by att.LOANPRODUCTKEY) AS sub2 ON att.LOANPRODUCTKEY = sub2.LOANPRODUCTKEY  group by att.loanproductkey   
Original en ingles

I am trying to create a query that will capture the working hours taken for an event to occur. In the data below, I'd want to show the working hours taken for an account to go from ACTIVATED to DEACTIVATED.

ENCODEDKEY  TRANSACTIONID   LOANPRODUCTKEY  TIMESTAMP              TYPE 1           2067            aa1             2015/02/06 15:29:00    LOAN_PRODUCT_ACTIVATED 2           2162            aa1             2015/02/16 14:07:00    LOAN_PRODUCT_EDITED 3           2666            aa1             2015/02/16 15:29:00    LOAN_PRODUCT_DEACTIVATED 4           3456            aa2             2015/03/06 12:01:00    LOAN_PRODUCT_ACTIVATED 5           3478            aa2             2015/03/08 13:15:00    LOAN_PRODUCT_EDITED 6           3908            aa2             2015/03/18 13:15:00    LOAN_PRODUCT_DEACTIVATED 

So the results would be something like

LOANPRODUCTKEY          TIME aa1                     24:00:00 aa2                     12:00:00 

(I know those numbers are wrong!)

I also need it to only consider working hours (i.e 9am to 5pm) Is this possible?

Thank you to anyone who can help me.

Update. Big thankyou to those who've helped so far!

So I have managed to create a query which will return the correct dates for the activated and deactivated typers per loanproductkey. However, I am still struggling to work out the working hours between the two calculated dates. My query is as follows:

SELECT  att.LOANPRODUCTKEY ,sub1.time_activated ,sub2.time_deactivated  from  activity att  left join (select min(att.TIMESTAMP) as time_activated ,att.loanproductkey from activity att where att.`TYPE` = "LOAN_PRODUCT_ACTIVATED" group by att.LOANPRODUCTKEY) AS sub1 ON att.LOANPRODUCTKEY = sub1.LOANPRODUCTKEY  left join  (select max(att.timestamp) as time_deactivated ,att.LOANPRODUCTKEY from activity att where att.`TYPE` = "LOAN_PRODUCT_DEACTIVATED" group by att.LOANPRODUCTKEY) AS sub2 ON att.LOANPRODUCTKEY = sub2.LOANPRODUCTKEY  group by att.loanproductkey 
        
         
         

Lista de respuestas

0
 
vote
vote
La mejor respuesta
 

OK, así que creo que la consulta a continuación debería funcionar, aunque probablemente pueda ser ajustada mucho. Es más probable que sea posible hacerlo más inteligente también, supongo :)

La lógica es la siguiente:

  1. Primero usa una tabla de números para generar la secuencia de fechas entre las fechas de inicio y finalización de cada producto préstamo (esto se realiza en una tabla derivada).

  2. Luego, se une a esta tabla y cuenta el número de días de trabajo completos entre el inicio y el final (exclusivo) y agrega esto (Times 240 para obtener minutos) a la cantidad de minutos que es la diferencia entre los días iniciales Tiempo y 17:00, y la diferencia en minutos entre las 09:00 y el tiempo final.

Entonces el cálculo es esto:

  (minutes from start time of first day to 17:00) -- eg. '17:00:00'-'15:29:00' + (minutes from 09:00 to end time of last day) -- eg. '15:29:00'-'09:00' + (number of working days between start and end) * 240   

Es posible que tenga que afinar un poco a las matemáticas, pero la lógica debe ser un sonido.

Asignaciones hechas:

  • Los fines de semana son los días de semana 5 y 6, según lo devuelto por la función var specialties = cf.dimension(function(d) { return d.specialties; }); specialties.filterFunction(function(d) { return d.indexOf("specialty") >= 0; }); 9 , esto podría depender de la configuración del servidor local, no estoy seguro.

  • que existe una tabla llamada Exchange0 con una columna Exchange1111 que contiene dígitos 1 a través de al menos el rango necesario para cubrir su intervalo de fecha máxima desde Empezar a finalizar . Si no tiene esto, he descrito cómo puede crearlo al final.

Esto obtiene un resultado como:

  Exchange2  

muestra el violín SQL (sin la columna Total_Time, ya que Fiddle usa Java, que No le gustan los valores grandes para la parte de la hora.

  Exchange3  

Si aún no tiene una tabla adecuada con una secuencia de números que cubre el número máximo de días entre las fechas de inicio y finalización, puede crear una tabla llena con el número 1-1000 utilizando la consulta a continuación, que tomé desde esta respuesta .

  Exchange4  
 

Ok, so I think the query below should work although it probably can be tweaked a lot. It's most likely possible to do it smarter too I guess :)

The logic is as follows:

  1. First it uses a number table to generate the sequence of dates between the start and end dates for every loanproductkey (this is done in a derived table).

  2. Then it joins this table and counts the number of whole working days between the start and end (exclusive) and adds this (times 240 to get minutes) to the number of minutes that is the difference between the starting days time and 17:00, and the difference in minutes between 09:00 and the ending days time.

So the calculation is this:

(minutes from start time of first day to 17:00) -- eg. '17:00:00'-'15:29:00' + (minutes from 09:00 to end time of last day) -- eg. '15:29:00'-'09:00' + (number of working days between start and end) * 240 

It's possible that you might have to fine-tune the math a bit but the logic should be sound.

Assumptions made:

  • Weekends are weekday 5 and 6 as returned by the weekday function - this might be dependent on local server settings, I'm not sure.

  • That there exists a table called numbers with a column num containing digits 1 through at least the range needed to cover your maximum date range from start to end. If you don't have this I've described how you can create it at the end.

This gets a result like:

LOANPRODUCTKEY  total_min   total_time (hhh:mm:ss) aa1             4800        80:00:00 aa2             5834        97:14:00 

Sample SQL Fiddle (without the total_time column as fiddle uses java which doesn't like large values for the hour part.

SELECT      t_start.LOANPRODUCTKEY      , (         TIMESTAMPDIFF(MINUTE, CAST(t_start.timestamp AS time), CAST('17:00:00' AS time))       + TIMESTAMPDIFF(MINUTE, CAST('09:00:00' AS time), CAST(t_end.timestamp AS time))       + COUNT(WEEKDAY(t_start.timestamp) NOT IN (5,6)) * 8 * 60     ) AS total_minutes      , SEC_TO_TIME(         TIMESTAMPDIFF(SECOND, CAST(t_start.timestamp AS time), CAST('17:00:00' AS time))        + TIMESTAMPDIFF(SECOND, CAST('09:00:00' AS time), CAST(t_end.timestamp AS time))        + COUNT(WEEKDAY(t_start.timestamp) NOT IN (5,6)) * 8 * 60 * 60     ) AS total_time  FROM      t t_start JOIN      t t_end ON t_start.LOANPRODUCTKEY = t_end.LOANPRODUCTKEY JOIN  (     SELECT          ts.LOANPRODUCTKEY         , DATE(DATE_ADD(ts.timestamp,INTERVAL num DAY)) AS datesSeries     FROM          t ts     JOIN          t te ON ts.LOANPRODUCTKEY = te.LOANPRODUCTKEY     CROSS JOIN         numbers r     WHERE          num < DATEDIFF(te.timestamp, ts.timestamp)       AND          ts.TYPE = 'LOAN_PRODUCT_ACTIVATED'      AND          te.TYPE = 'LOAN_PRODUCT_DEACTIVATED' ) dates ON t_start.LOANPRODUCTKEY = dates.LOANPRODUCTKEY  WHERE      t_start.TYPE = 'LOAN_PRODUCT_ACTIVATED' AND t_end.TYPE = 'LOAN_PRODUCT_DEACTIVATED' GROUP BY      t_start.LOANPRODUCTKEY, t_start.TIMESTAMP, t_end.TIMESTAMP ORDER BY      t_start.LOANPRODUCTKEY; 

If you don't already have a suitable table with a number sequence that covers the maximum number of days between the start and end dates you can create a table filled with the number 1-1000 using the query below, which I took from this answer.

CREATE TABLE numbers (num int primary key);  INSERT INTO numbers SELECT SEQ.SeqValue FROM (     SELECT (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue     FROM (         SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue UNION ALL         SELECT 2 SeqValue UNION ALL SELECT 3 SeqValue UNION ALL         SELECT 4 SeqValue UNION ALL SELECT 5 SeqValue UNION ALL         SELECT 6 SeqValue UNION ALL SELECT 7 SeqValue UNION ALL         SELECT 8 SeqValue UNION ALL SELECT 9 SeqValue     ) ONES     CROSS JOIN (         SELECT  0 SeqValue UNION ALL SELECT 10 SeqValue UNION ALL         SELECT 20 SeqValue UNION ALL SELECT 30 SeqValue UNION ALL         SELECT 40 SeqValue UNION ALL SELECT 50 SeqValue UNION ALL         SELECT 60 SeqValue UNION ALL SELECT 70 SeqValue UNION ALL         SELECT 80 SeqValue UNION ALL SELECT 90 SeqValue     ) TENS     CROSS JOIN (         SELECT   0 SeqValue UNION ALL SELECT 100 SeqValue UNION ALL         SELECT 200 SeqValue UNION ALL SELECT 300 SeqValue UNION ALL         SELECT 400 SeqValue UNION ALL SELECT 500 SeqValue UNION ALL         SELECT 600 SeqValue UNION ALL SELECT 700 SeqValue UNION ALL         SELECT 800 SeqValue UNION ALL SELECT 900 SeqValue     ) HUNDREDS ) SEQ WHERE SEQ.SeqValue > 0; 
 
 
     
     

Relacionados problema

3  ¿Cómo obtener la hora y la hora más cercana?  ( How to get the hour and the nearest hour ) 
¿Cómo obtiene la hora actual y la hora más cercana, por ejemplo? Ahora es 2:25PM . La hora actual es 2:00PM La hora más cercana para ser 2:59PM...

-1  Diferencia entre 2 fechas usando jQuery? CALCUALATE Número de vacaciones dentro del período de tiempo [duplicado]  ( Difference between 2 dates using jquery calcualate number of holidays within th ) 
Esta pregunta ya tiene respuestas aquí : ¿Get Diferencia entre 2 fechas en JavaScript? [duplicado] ...

32  ¿Cómo establece el tiempo y solo la hora en un calendario en Java?  ( How do you set the time and only the time in a calendar in java ) 
Tener las horas y los minutos, ¿hay alguna forma más fácil o mejor de configurarlo en un objeto calendario que: calendar.set(calendar.get(Calendar.YEAR), ...

1  DATEDIFF REDECTORIO DE DESCUENTO DE CUTÍFICA FECHA HORAS SQL Server  ( Datediff round off cutoff date hours sql server ) 
MI CLIENTE TIENE ESTA REGLA DE LA HORA DE KTV TODA LA ESPERA POR MAYOR DE 20 MINOS SE CONSIDERARÁ ASO ADICIONAL 1 HORA. Código de ejemplo a continuación: ...

0  Horas de entrada - aplicación iónica  ( Input hours ionic app ) 
He estado tratando de crear un menú desplegable para mi aplicación iónica donde un usuario puede seleccionar diferentes veces también reservar una cita. Lo co...

0  Hora, diferencia de minuto en PHP  ( Hour minute difference in php ) 
Estoy buscando una solución en PHP. Tengo un tiempo total para una tarea en HH: MM: Formato SS. (La fecha no es importante). Y estoy entrando cada hora de t...

1  ¿Los datos de subconjunto para un día si los datos entre dos horas del día se reúnen con los criterios?  ( Subset data for a day if data between two hours of the day meets criteria ) 
Soy bastante nuevo en R y sería genial si pudieras ayudar con este problema, ya que no he podido encontrar alguna respuesta a este problema en línea. Esto es ...

72  Añadir 'x' Número de horas hasta la fecha  ( Add x number of hours to date ) 
Actualmente tengo PHP que devuelve la fecha / hora actual, así: $now = date("Y-m-d H:m:s"); Lo que me gustaría hacer es tener una nueva variable $new_...

0  Formato de SQLite Fecha de hora a hora  ( Sqlite format date from hour to hour ) 
Quiero almacenar en mi fecha de base de datos SQLite en formato DD / MM / YYAY HH: MM-HH: MM ¿Hay alguna solución de esto? Solo encontré que YYYY-MM-DDTHH: MM...

0  JQUERY - CAMBIAR 1-24 HORA A 1-12 HORA DE USO. ¿MÉTODO DE COTOTES ()?  ( Jquery change 1 24 hour to 1 12 hour using gethours method ) 
Fiddle: http://jsfiddle.net/bnsex/1/ Quiero usar el reloj de 12 horas para este código ... $(document).ready(function() { setInterval( function() { va...




© 2022 respuesta.top Reservados todos los derechos. Centro de preguntas y respuestas reservados todos los derechos