How to find differences of two time value in SQL using java?

Question

I have a java class that gives me time_in and time_out. Time_in and time_out are also tables in my sql database. I'd like to find work hours which can be calculated as time_out - time_in. Time_in and time_out are time format (hh:mm:ss). How to find the work hour ? The code that I tried doesn't work becuase of the data variable type(I used String). But I couldn't do this with the int data type anyway. Here is the class that I have. I didn't put time_out for saving the space. Pretend that there is another methods for retrieving the time_out just like time_in.

import java.sql.*;


public class blah {

    //------MONDAY-------
    private static String timeInQMonday;
    private static String timeInMonday;


    public static void main(String[] args) {

        loadTimeInMonday();
        loadWorkHour();

    }

    public static void loadWorkHour(){
        //workHour = time_out - time_in;
    }

    public static void loadTimeInMonday(){
                try {
                     Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                     String url = "jdbc:sqlserver://SERVER2\\WASPDBEXPRESS;databaseName=WaspTime;integratedSecurity=true;";
                     Connection conn = DriverManager.getConnection(url);
                     System.out.println("Connection established!");

                     //Creating the statement
                     Statement stmt = conn.createStatement();
                     timeInQMonday = "SELECT CONVERT(VARCHAR(8), time_in, 108) AS time_in  FROM [WaspTime].[dbo].[job_punch_card]  WHERE emp_key=55 and punch_day= DATEADD(week, DATEDIFF(day, 0, getdate())/7, 0)";

                     try {
                         stmt = conn.createStatement();
                         ResultSet rsTimeIn = stmt.executeQuery(timeInQMonday);
                         //-------DISPLAY THE RESULT-----

                         while (rsTimeIn.next()) {
                              timeInMonday = rsTimeIn.getString("time_in");

                              if(timeInMonday==null){
                                  System.out.println("No Data");
                              }else{
                                  System.out.println("clock in: " + timeInMonday);
                              }               
                            }

                       ///////////////////////////////  

                     } catch (SQLException e ) {
                         System.out.println(e);
                     } finally {
                         if (stmt != null) { stmt.close(); }
                     }

                } catch (Exception e) {
                    System.out.println("Connection Failed");
                }
    }
}

score 1 · Accepted Answer · answered Dec 15 '14 at 19:10

I'm not sure I fully understand the question details, but if I'm correct you want the number of hours worked. Here is code that you can use to that end:

import java.util.*;
import java.lang.*;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.concurrent.TimeUnit;

class HoursWorked {
    public static void main (String[] args) throws java.lang.Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:SS");
        Date timeIn = sdf.parse("01:00:00");
        Date timeOut = sdf.parse("09:30:00");
        long workHourInMillis = timeOut.getTime() - timeIn.getTime();
        System.out.println("Hours worked: " + TimeUnit.MILLISECONDS.toHours(workHourInMillis));
    }
}

score 0 · Answer 2 · answered Dec 15 '14 at 18:57

0

Parse String into Date using SimpleDateFormat.

Convert Date to Calender.

Get hour from calender. calender.get(Calender.HOUR).

From that you can find the diff

answered Dec 15 '14 at 18:57

Siva Kumar

1,983
3
14
26

How to find differences of two time value in SQL using java?

2 Answers2