I intended to do a group function in my hibernate 2.1 query on mysql 4, that tells me the total times a user has been working. But unfortunately my date function is always giving me wrong values.
For example, If I have one entry that tells me the user began his session at (BEGIN_TIME) 2005-03-02 15:00:48 and ended it at 2005-03-02 18:00:48 the Result would be "9". Now obviously, that can't be right. What am I missing here? Sorry if this is off topic.
I am using:
select MONTH(t.endTime), HOUR(sum(t.endTime - t.beginTime)) from TimeSlice t, User u where t.user = 1 and t.parentEntryId is null and u.parentEntryId is null GROUP BY MONTH(t.endTime)
well actually when it's executed it gets evaluated into this:
select MONTH(timeslice0_.END_TIME) as x0_0_, HOUR(sum(timeslice0_.END_TIME-timeslice0_.BEGIN_TIME)) as x1_0_ from TIMESLICE timeslice0_, USER user1_ where (timeslice0_.REF_USER_ID=1 ) and(timeslice0_.PARENT_ID is null ) and(user1_.PARENT_ID is null ) group by MONTH(timeslice0_.END_TIME)
on these tables:
create table TIMESLICE ( ID bigint not null, REF_TASK_ID bigint not null, REF_USER_ID bigint not null, REF_WORKSTATUS_ID bigint not null, PARENT_ID bigint, CREATE_DATE datetime, CREATE_USER bigint not null, CHANGE_DATE datetime not null, CHANGE_USER bigint not null, BEGIN_TIME datetime not null, BREAK_TIME bigint not null, END_TIME datetime not null, JOB_DESCRIPTION varchar(255), ACTIVE bit not null, primary key (ID) );
create table USER ( ID bigint not null, EMPL_TYPE bigint, DIVISION_ID bigint, EMAIL varchar(80) not null, PASSWORD varchar(50) not null, ACTIVE bit not null, FIRSTNAME varchar(50) not null, INITIALNAME varchar(5), LASTNAME varchar(50) not null, TITLE varchar(20), USERLEVEL integer not null, USERNAME varchar(10) not null, PARENT_ID bigint, CREATE_DATE datetime, CREATE_USER bigint not null, CHANGE_DATE datetime not null, CHANGE_USER bigint not null, primary key (ID) );
Joined: Feb 21, 2005
The Problem just solved itself Turns out I forgot a lot about how sql works. Thanks anyways.