0

I will try to explain what I want to do. I have a table in which I store the time that a user enters to work and update this row when the user leaves his work. The fields are timeIn and timeOut. Before I store the timeOut, I want to display how many hours the user has worked so far. So I think I have to retrieve the timeIn and calculate the difference with the actual hour of the system. But I don't know how to retrieve only one field of a table. I reckon I have to create an object time(for example) and get the timeIn along with other parameters and then calculate the difference. But I don't know whether I'm right and how to do that.

Cheers

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Camus
  • 827
  • 2
  • 20
  • 36

1 Answers1

1

See here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff

You may be able to get the hours worked straight from MySQL thusly:

SELECT TIMEDIFF(NOW(), timeIn) AS hoursWorked
FROM yerTable
WHERE personIdOrWhatever...

You didn't tell us what type of field timeIn is. It should be datetime.

Andrew
  • 4,574
  • 26
  • 31
  • timeIn and timeOut is timestamp. I din't understand AS hoursworked. What AS does mean? – Camus Feb 18 '12 at 02:53
  • And another question is: Will I have to storage the result in a resultSet? – Camus Feb 18 '12 at 03:05
  • AS is a way to name a field. It helps to explain what your code is doing. As for the ResultSet, that could take some explaining. There's a tutorial here: http://docs.oracle.com/javase/tutorial/jdbc/basics/ that should help you get started. – Andrew Feb 18 '12 at 17:34
  • So if I do a select like this I still have to storage in the resultset object, right? But how I can get the result as String. I'm using prepared statement. Hence, I have to indicate the name of the column that I'm retrieving the result, getString("hoursworked")? – Camus Feb 19 '12 at 02:46
  • and another thing is, I've tried this query and it is returning null; however, when I replace now() to time_out it works. Do you know why? – Camus Feb 19 '12 at 03:07
  • Both arguments to TIMEDIFF must be the same type. Try TIMEDIFF(NOW(), TIMESTAMP(timein)) to convert timein to a datetime value. – Andrew Feb 19 '12 at 15:38
  • I've tried this query and it's worked: SELECT TIMEDIFF (current_time, time_in) AS hoursWorked FROM date_time WHERE id_date_time = ? I'm just trying to make it work in my code. To retrieve this information, can I use String h = result.getString("hoursWorked")?? – Camus Feb 20 '12 at 12:28
  • Did you understand what I meant? In the query test, it is working, I'm getting the result but I don't know how to retrieve to my string in my java code. – Camus Feb 22 '12 at 04:05
  • This might help http://stackoverflow.com/questions/3323618/handling-mysql-datetimes-and-timestamps-in-java and/or http://stackoverflow.com/questions/7848223/how-to-convert-date-in-java – Andrew Feb 22 '12 at 15:37