I have a doubt with this problem apparently when doing the select and doing the operation with the method TIMESTAMPDIFF of MySQL I get the following error:
java.sql.SQLException: java.sql.SQLException: Illegal hour value '58' for java.sql.Time type in value '58:13:01. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) at com.mysql.jdbc.ResultSetRow.getTimeFast(ResultSetRow.java:970) at com.mysql.jdbc.ByteArrayRow.getTimeFast(ByteArrayRow.java:223) at com.mysql.jdbc.ResultSetImpl.getTimeInternal(ResultSetImpl.java:5926) at com.mysql.jdbc.ResultSetImpl.getTime(ResultSetImpl.java:5662) at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4898) at Gerencia_Proyectos.frmIndicadores.jButton1MouseClicked(frmIndicadores.java:116) at Gerencia_Proyectos.frmIndicadores.access$000(frmIndicadores.java:23) at Gerencia_Proyectos.frmIndicadores$1.mouseClicked(frmIndicadores.java:64) at java.awt.AWTEventMulticaster.mouseClicked(AWTEventMulticaster.java:270) at java.awt.Component.processMouseEvent(Component.java:6536) at javax.swing.JComponent.processMouseEvent(JComponent.java:3324) at java.awt.Component.processEvent(Component.java:6298) at java.awt.Container.processEvent(Container.java:2236) at java.awt.Component.dispatchEventImpl(Component.java:4889) at java.awt.Container.dispatchEventImpl(Container.java:2294) at java.awt.Component.dispatchEvent(Component.java:4711) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4534) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466) at java.awt.Container.dispatchEventImpl(Container.java:2280) at java.awt.Window.dispatchEventImpl(Window.java:2746) at java.awt.Component.dispatchEvent(Component.java:4711) at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758) at java.awt.EventQueue.access$500(EventQueue.java:97) at java.awt.EventQueue$3.run(EventQueue.java:709) at java.awt.EventQueue$3.run(EventQueue.java:703) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86) at java.awt.EventQueue$4.run(EventQueue.java:731) at java.awt.EventQueue$4.run(EventQueue.java:729) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76) at java.awt.EventQueue.dispatchEvent(EventQueue.java:728) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93) at java.awt.EventDispatchThread.run(EventDispatchThread.java:82) Caused by: java.sql.SQLException: Illegal hour value '58' for java.sql.Time type in value '58:13:01. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) at com.mysql.jdbc.TimeUtil.fastTimeCreate(TimeUtil.java:1023) at com.mysql.jdbc.ResultSetImpl.fastTimeCreate(ResultSetImpl.java:1012) at com.mysql.jdbc.ResultSetRow.getTimeFast(ResultSetRow.java:964) ... 39 more
I read there and I think it's because in java it only allows me to operate up to 24 hours, that's why I get the error because it exceeds, it's 58 hours. On the other hand, if I try my query in MySQL if it works, but not in Java.
I leave the query:
select
sec_to_time(TIMESTAMPDIFF(second,r.fecha_envio_rq,a.fecha_aprobacion)) as 'Tiempo demorado entre fecha de envio y autorizacion',
sec_to_time(TIMESTAMPDIFF(second,a.fecha_aprobacion,vba.fecha_aprobacion)) as 'Tiempo demorado entre fecha de autorizacion y almacén',
sec_to_time(TIMESTAMPDIFF(second,vba.fecha_aprobacion,vbl.fecha_aprobacion)) as 'Tiempo demorado entre fecha de almacén y logística',
sec_to_time(TIMESTAMPDIFF(second,vbl.fecha_aprobacion,vbo.fecha_aprobacion)) as 'Tiempo demorado entre fecha de logística y obra',
sec_to_time(TIMESTAMPDIFF(second,r.fecha_envio_rq,vbo.fecha_aprobacion)) as 'Tiempo total demorado'
from requerimiento r
join autorizacion a on r.cod_rq=a.cod_rq
join vb_almacen vba on r.cod_rq=vba.cod_rq
join vb_logistica vbl on r.cod_rq=vbl.cod_rq
join vb_obra vbo on r.cod_rq=vbo.cod_rq
where r.cod_rq = '2'
And I execute it in the following way:
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conexion = DriverManager.getConnection("jdbc:mysql://192.168.0.14:3306/bd_requerimiento", "root", "SistemasDaca");
Statement st = conexion.createStatement();
ResultSet rst = st.executeQuery(" select sec_to_time(TIMESTAMPDIFF(second,r.fecha_envio_rq,a.fecha_aprobacion)) as 'Tiempo demorado entre fecha de envio y autorizacion',sec_to_time(TIMESTAMPDIFF(second,a.fecha_aprobacion,vba.fecha_aprobacion)) as 'Tiempo demorado entre fecha de autorizacion y almacén',sec_to_time(TIMESTAMPDIFF(second,vba.fecha_aprobacion,vbl.fecha_aprobacion)) as 'Tiempo demorado entre fecha de almacén y logística',sec_to_time(TIMESTAMPDIFF(second,vbl.fecha_aprobacion,vbo.fecha_aprobacion)) as 'Tiempo demorado entre fecha de logística y obra',sec_to_time(TIMESTAMPDIFF(second,r.fecha_envio_rq,vbo.fecha_aprobacion)) as 'Tiempo total demorado' from requerimiento r join autorizacion a on r.cod_rq=a.cod_rq join vb_almacen vba on r.cod_rq=vba.cod_rq join vb_logistica vbl on r.cod_rq=vbl.cod_rq join vb_obra vbo on r.cod_rq=vbo.cod_rq where r.cod_rq ="+txtcod_rq.getText()+ "");
ResultSetMetaData rsmt = rst.getMetaData();
int nrocolumnas = rsmt.getColumnCount();
DefaultTableModel modelo = new DefaultTableModel();
this.tablaindicadores.setModel(modelo);
for (int x = 1; x <= nrocolumnas; x++) {
modelo.addColumn(rsmt.getColumnLabel(x));
}
while (rst.next()) {
Object[] fila = new Object[nrocolumnas];
for (int y = 0; y < nrocolumnas; y++) {
fila[y] = rst.getObject(y + 1);
}
modelo.addRow(fila);
}
} catch (ClassNotFoundException ce) {
ce.printStackTrace();
} catch (SQLException se) {
se.printStackTrace();
}
I enclose a photo of the test in MySQL, so it should come out in java :(.
I would appreciate any help comments, thank you very much.