1

I have written this fiddle

CREATE TABLE task
    (`taskid` int, `description` varchar(255),
    PRIMARY KEY (`taskid`))
;

INSERT INTO task
    (`taskid`, `description`)
VALUES
    (1, 'Platform for building enterprise provisioning solutions'),
    (2,'Full-featured access management'),
    (3, 'Robust LDAP server for Java')
;

CREATE TABLE worktime
    (`taskid` int, `user` varchar(7), `startwork` datetime , `endwork` datetime)
;

INSERT INTO worktime
    (`taskid`, `user`, `startwork`, `endwork`)
VALUES
    (1, 'dirk', '2015-07-26 15:19:46', '2015-07-26 15:20:46'),
    (1, 'dirk', '2015-07-26 17:19:46', '2015-07-26 18:11:46'),
    (2, 'dirk', '2011-02-26 11:19:46', '2011-02-26 12:11:46'),
    (2, 'tim', '2011-02-26 11:19:46', '2011-02-26 12:11:46')
;

What I want is : Find the total time a user spent on a task.

E.g. say for task 1 and user dirk this would be 01:01 hours:minutes for task 1.

How can i create this view?

1

it's better to add a userID to your worketime table instead of User and also the name user is reserved word so don't use it. The view you are looking for is made like this

CREATE VIEW user_task_time as select `user`,taskid,sec_to_time(sum(TIME_TO_SEC(TIMEDIFF(endwork,startwork)))) as timeWorked FROM worktime GROUP BY user,taskid;

TIMEDIFF() return a time value and it is converted to second and back to time because SUM() function does not return correct results when used by time data type.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.