File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Mysql Date problems Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Mysql Date problems" Watch "Mysql Date problems" New topic
Author

Mysql Date problems

Johann Spitz
Greenhorn

Joined: Feb 21, 2005
Posts: 11
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

Joined: Feb 21, 2005
Posts: 11
The Problem just solved itself Turns out I forgot a lot about how sql works. Thanks anyways.
 
 
subject: Mysql Date problems