This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Mysql Date problems

 
Johann Spitz
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
);
 
Johann Spitz
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The Problem just solved itself Turns out I forgot a lot about how sql works. Thanks anyways.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic