lalthung lalthung - 4 months ago 9
MySQL Question

Fetch total hours from timin and timout table someone?

i have this two tables which when user logs in i store current stimestamp to in timein table with the fields id int 11 auto inc, user_id and time the similaer way i do for logout now my question is how could i get total login hours by calculating from timein and timeout tables with the fields time which is current time stamp? day and month wise

this is how my schema looks like

CREATE TABLE IF NOT EXISTS `timein` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`member_id` int(11) NOT NULL,
`member_name` varchar(32) NOT NULL,
`team` varchar(32) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `timeout` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`member_id` int(11) NOT NULL,
`member_name` varchar(32) NOT NULL,
`team` varchar(32) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Answer

For calculate hour use timestampdiff function

    select *,concat(timestampdiff(hour, a.time, b.time),' hours') from timeout b,timein a
where a.id=b.id
Comments