How to convert "hh:mm:ss" into interval for sql update

Question

I have table in sql where has to be a total time that each employee worked for a day and because they can leave the office during the worktime I need to do a sum of all the times they were in the office.

this is my code (I simplified it because it is taken out of context):


create table hours_per_day
(
    id serial primary key,
    day timestamp,
    worked_hours timestamp, 
    id_employee integer references employees(id) ON DELETE SET NULL

);

then I get this:


System.out.println("Date of leave:    ");
String dat = br.readLine();

System.out.println("Time of leave:    ");
String time = br.readLine();



String tiime = dat + " " + time;

SimpleDateFormat format=new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
Date date=format.parse(tiime);
mainentry.setCheckOut(new java.sql.Timestamp(date.getTime()));


WorkedHoursPerDay odpr = new WorkedHoursPerDay();
SimpleDateFormat formatt=new SimpleDateFormat("dd/MM/yyyy");
Date dateee=formatt.parse(dat);
odpr.setDay(new java.sql.Timestamp(dateee.getTime()));

long dif = mainentry.getCheckOut().getTime() - z.getCheckIn().getTime();

        String hms = String.format("%02d:%02d:%02d", TimeUnit.MILLISECONDS.toHours(dif),
        TimeUnit.MILLISECONDS.toMinutes(dif) - TimeUnit.HOURS.toMinutes(TimeUnit.MILLISECONDS.toHours(dif)),
        TimeUnit.MILLISECONDS.toSeconds(dif) - TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(dif)));

        String nullls = "00:00:00";

        String beg = dat + " " + nullls;

        SimpleDateFormat form=new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
        Date dattee=form.parse(beg);
        odpr.setWorkedHours(new java.sql.Timestamp(dattee.getTime()));

odpr.insert();
odpr.update();

this is my code:


public void update() throws SQLException {
        if (idEmployee== null) {
            throw new IllegalStateException("id employee does not exist");
        }

        try (PreparedStatement s = DbContext.getConnection().prepareStatement("UPDATE hours_per_day SET worked_hours = worked_hours + ? WHERE id_employee = ? AND day = ?")) {

            s.setTimestamp(1, howManyHoursWorked)     
            s.setInt(2, idEmployee);
            s.setTimestamp(3, day);



            s.executeUpdate();
        }
    }

but this is the result:

ERROR: invalid input syntax for type interval: "2019-01-01 04:00:00+01"

I don't know how to add those worked hours to already existing hours in database. Maybe I should change the String time1 into interval but I dont know how to do that.

@Srinika What do you mean? I'm sorry English is not my first language. If you mean whether he can leave a lot of times then no. He can leave office only for one break per a day and then when he goes home. — dfps12, May 08 '20 at 00:46
if the employee, has multiple records of in and out per day, you need to have all that data. for each of those intervals you need to find the # of hours in the office. And also, you seems to be getting the # of hours calculated in the client side (in java) . I think you better do it in the database, as all the other data is there. Otherwise, you need to find the hours in java Which one is your choice. There is not enough info. — Srinika Pinnaduwage, May 08 '20 at 00:52
@Srinika No, he doesn't. Yes, it is calculated in java because I get the timestamp of when he got to work, when he left, came back and left for home from input. Then I calculate String time1 and I need to add time1 to String time (which is already in the database). I hope I explained it a bit better. — dfps12, May 08 '20 at 00:58
put all (date time related data sample for an employee) that you get and what you need as output and the datatype of the column in the database (and whether the database is oracle or SQL Server or what) — Srinika Pinnaduwage, May 08 '20 at 01:01
@Srinika I edited the post, my code is kind of chaotic and very long so I picked what I think is important. I hope it helped. — dfps12, May 08 '20 at 01:17

score 0 · Answer 1 · answered May 08 '20 at 00:57

if it is SQL, here is how you get the time difference

declare @st1 datetime = '01/01/2019 00:00:00',  @st2 datetime = '01/01/2019 04:00:00'

Declare @TimeInOffice int
Select @TimeInOffice = DateDiff(hh,@st1, @st2)
Select @TimeInOffice

And please have a look at the following for hrs difference in Java.

How to convert "hh:mm:ss" into interval for sql update

1 Answers1