This week's book giveaways are in the AI and JavaScript forums.
We're giving away four copies each of GANs in Action and WebAssembly in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of GANs in ActionE this week in the AI forum
or WebAssembly in Action in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
  • Knute Snortum
Sheriffs:
  • Liutauras Vilda
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Joe Ess
  • salvin francis
  • fred rosenberger

Mysql Date problems

 
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.
 
What are your superhero powers? Go ahead and try them on this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!