best way to store login hours?

[lovephp]

What should the query be like to store attendance and also working hours for night shift workers the date changes by the time work gets over. M clueless how to achieve this as to get total records of working hours month wise. I did the attendance part also added field as in ams out time with time mysql field on a date timestamp but something don't seem ok.

 

Any suggestions would be helpful

[requinix]

Don't store login hours. Store login timestamps. Full DATETIME/TIMESTAMP values. And if you have each work period storing a login/logout pair then you can SUM the diffs.

[lovephp]

Thanks but you know by the time its logoff time its a different date already and how would i get total for the month if last day of the month work ends at the first day of Next month?.

 

Is there also a way to assign a particular id to all those rows along while loop? Autoincrement adds different ids for all todays attendance its hard to fetch all if they don't have a similar id.

[lovephp]

Don't store login hours. Store login timestamps. Full DATETIME/TIMESTAMP values. And if you have each work period storing a login/logout pair then you can SUM the diffs.

Yes i did try datetime fields for time in and time out

[requinix]

Thanks but you know by the time its logoff time its a different date already

That only holds for overnight shifts. Are you specifically targeting only those and want to deliberately prevent your system from being compatible with daytime shifts?

 

What about a case where an overnight worker has an emergency and leaves during the same day? Assuming logoff is the next day will automatically credit the person with 24h more than they should, not to mention potentially conflict with a shift the next day.

 

and how would i get total for the month if last day of the month work ends at the first day of Next month?.

Depends. What does the business think? It's probably determined based on the start date: "if start date is within month then sum(end date - start date)."

 

Is there also a way to assign a particular id to all those rows along while loop? Autoincrement adds different ids for all todays attendance its hard to fetch all if they don't have a similar id.

So you want duplicate IDs? Don't. More appropriate would be to store login and logoff in the same row.

[lovephp]

That only holds for overnight shifts. Are you specifically targeting only those and want to deliberately prevent your system from being compatible with daytime shifts?

 

What about a case where an overnight worker has an emergency and leaves during the same day? Assuming logoff is the next day will automatically credit the person with 24h more than they should, not to mention potentially conflict with a shift the next day.

 

Depends. What does the business think? It's probably determined based on the start date: "if start date is within month then sum(end date - start date)."

 

So you want duplicate IDs? Don't. More appropriate would be to store login and logoff in the same row.

th

 

That only holds for overnight shifts. Are you specifically targeting only those and want to deliberately prevent your system from being compatible with daytime shifts?

 

What about a case where an overnight worker has an emergency and leaves during the same day? Assuming logoff is the next day will automatically credit the person with 24h more than they should, not to mention potentially conflict with a shift the next day.

 

Depends. What does the business think? It's probably determined based on the start date: "if start date is within month then sum(end date - start date)."

 

So you want duplicate IDs? Don't. More appropriate would be to store login and logoff in the same row.

is is really confusing with the night shift thing you are right if a employee has to leave early and also with login and logout supposing employee gets logout for some reason then also there will be a conflict i guess manually enter the hours like 8 for a day next is say 5 hours the other day would that be a better idea? then calculating the total for a month also would not be an issue right?

[requinix]

Either you shouldn't care about what day is what, or you need to find out (or decide) how the business itself answers the question. Like I said, it's probably about the start date: if I work Sunday 10pm to Monday 6am then I would say my "Sunday" shift was 8 hours long.

 

Maybe I'm not understanding what is so confusing about this?

[kicken]

how would i get total for the month if last day of the month work ends at the first day of Next month?.

There are three ways you could handle this:

1) Count the entire shift as part of the month it started in

2) Count the entire shift as part of the month it ended in

3) Try and split the time.

 

This is ultimately a business decision. You need to ask your client (or yourself) which method you want to use and then code for that method. #1 is probably the most common and what most people would expect. Its also relatively easy to do. You'd just query the database for shifts starting within the month then sum the difference between their in and out times.

 

SELECT empid, time_in, time_out, TIMEDIFF(time_out, time_in) duration
FROM timelog
WHERE
    time_in >= '2016-07-01 00:00:00'
    time_in < '2016-08-01 00:00:00'

[lovephp]

i created this two tables

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 ;

what would be the way to display daily or monthly total hours of particulat team? i know i got to join tables but i am not sure how to do that

 

i tried this but no results

$mnt = date("2016-07-30");

SELECT * FROM timein LEFT JOIN timeout WHERE timein.time LIKE '%".$mnt."%' AND = timeout.time LIKE '%".$mnt."%'AND team = 'TeamA'

Edited July 29, 2016 by lovephp

[lovephp]

Either you shouldn't care about what day is what, or you need to find out (or decide) how the business itself answers the question. Like I said, it's probably about the start date: if I work Sunday 10pm to Monday 6am then I would say my "Sunday" shift was 8 hours long.

 

Maybe I'm not understanding what is so confusing about this?

im just storing it on the 2 that would be a better idea i guess but now the issue is fetching the data

[kicken]

I would recommend using one table with two columns. There's no good way to join your two tables in order to get the duration between the time-in and time-out records. A single table means you don't have to duplicate the other fields either.

 

CREATE TABLE timelog (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    member_id int NOT NULL,
    time_in DATETIME NOT NULL,
    time_out DATETIME NULL
);

When someone wants to clock in, you insert a new row with the time_in value set to the current time and time_out NULL.

INSERT INTO timelog (member_id, time_in) values (1, UTC_TIMESTAMP())

When they clock out, you'd update the row's time_out value with the current timestamp

UPDATE timelog SET time_out=UTC_TIMESTAMP() WHERE member_id=1 AND time_out IS NULL

You'll need to check when doing a clock-in that there is no existing record with a NULL time_out column. In other words, prevent someone from clocking in twice without clocking out first.

 

Then to get your monthly hours tally you'd use a query like I showed above.

SELECT member_id, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(time_out, time_in)))) duration
FROM timelog
WHERE
    time_in >= '2016-06-01 00:00:00'
    AND time_in < '2016-07-01 00:00:00'
GROUP BY member_id

Fiddle

[lovephp]

I would recommend using one table with two columns. There's no good way to join your two tables in order to get the duration between the time-in and time-out records. A single table means you don't have to duplicate the other fields either.

 

CREATE TABLE timelog (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    member_id int NOT NULL,
    time_in DATETIME NOT NULL,
    time_out DATETIME NULL
);

When someone wants to clock in, you insert a new row with the time_in value set to the current time and time_out NULL.

INSERT INTO timelog (member_id, time_in) values (1, UTC_TIMESTAMP())

When they clock out, you'd update the row's time_out value with the current timestamp

UPDATE timelog SET time_out=UTC_TIMESTAMP() WHERE member_id=1 AND time_out IS NULL

You'll need to check when doing a clock-in that there is no existing record with a NULL time_out column. In other words, prevent someone from clocking in twice without clocking out first.

 

Then to get your monthly hours tally you'd use a query like I showed above.

SELECT member_id, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(time_out, time_in)))) duration
FROM timelog
WHERE
    time_in >= '2016-06-01 00:00:00'
    AND time_in < '2016-07-01 00:00:00'
GROUP BY member_id

Fiddle

 

thanks mate you are actually right this way is easier but with 2 saperate tables i was being able to avoid multiple entires for same date till user dont click logout it wont optout

[Barand]

       +--------------+       +----------------+       +--------------------+                                             

       | team         |       | member         |       | timeworked         |                                             

       +--------------+       +----------------+       +--------------------+                                             

       | team_id (PK) |---+   | member_id (PK) |---+   | timeworked_id (PK) |                                             

       | teamname     |   +--<| team_id        |   +--<| member_id          |                                             

       +--------------+       +----------------+       | timein             |                                             

                                                       | timeout            |                                             

                                                       +--------------------+                 

[lovephp]

       +--------------+       +----------------+       +--------------------+                                             
       | team         |       | member         |       | timeworked         |                                             
       +--------------+       +----------------+       +--------------------+                                             
       | team_id (PK) |---+   | member_id (PK) |---+   | timeworked_id (PK) |                                             
       | teamname     |   +--<| team_id        |   +--<| member_id          |                                             
       +--------------+       +----------------+       | timein             |                                             
                                                       | timeout            |                                             
                                                       +--------------------+                 

thanks you @Barand you are right about the teams i should have a separate table for that and yes i get now the time work table should be appreciate you both for the time. let me give this a try and i will get back.

[lovephp]

I would recommend using one table with two columns. There's no good way to join your two tables in order to get the duration between the time-in and time-out records. A single table means you don't have to duplicate the other fields either.

 

CREATE TABLE timelog (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    member_id int NOT NULL,
    time_in DATETIME NOT NULL,
    time_out DATETIME NULL
);

When someone wants to clock in, you insert a new row with the time_in value set to the current time and time_out NULL.

INSERT INTO timelog (member_id, time_in) values (1, UTC_TIMESTAMP())

When they clock out, you'd update the row's time_out value with the current timestamp

UPDATE timelog SET time_out=UTC_TIMESTAMP() WHERE member_id=1 AND time_out IS NULL

You'll need to check when doing a clock-in that there is no existing record with a NULL time_out column. In other words, prevent someone from clocking in twice without clocking out first.

 

Then to get your monthly hours tally you'd use a query like I showed above.

SELECT member_id, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(time_out, time_in)))) duration
FROM timelog
WHERE
    time_in >= '2016-06-01 00:00:00'
    AND time_in < '2016-07-01 00:00:00'
GROUP BY member_id

Fiddle

 

i got issues updating clock out time, currently im trying this

 

i added this to my logout.php but it just wont update the time_out field

 

 

$dt = date("Y-m-d h:i:s");      
mysql_query("UPDATE hours SET time_out = '".$dt."' WHERE member_id IN (SELECT MAX(hours_id) FROM hours WHERE member_id = '".$_SESSION['MEMBER_ID']."'");

 

but if i do insert it does ill show you my schema

 

 

--
-- Table structure for table `hours`
--

CREATE TABLE IF NOT EXISTS `hours` (
  `hours_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_in` datetime NOT NULL,
  `time_out` datetime DEFAULT NULL,
  `dated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`hours_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `hours`
--

 

what could i be doing wrong now?

[lovephp]

what is wrong in this query

 

 

$dt = date("Y-m-d h:i:s");    
$sql = "UPDATE hours SET time_out = '".$dt."' WHERE member_id IN (SELECT MAX(hours_id) FROM hours WHERE member_id = '".$_SESSION['MEMBER_ID']."'";
mysql_query($sql) or die(mysql_error());

 

that is gives me this error

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[lovephp]

fixed ) was missing but still it wont update the max hours_id of a user

[lovephp]

finally happening what i was looking for

 

$query = "select member_id, member_name, team, time_in, time_out, sec_to_time(unix_timestamp(time_out) - unix_timestamp(time_in)) AS totalhours from hours WHERE member_id ='7'";
$result = mysql_query($query)or die(mysql_error());
$rowNo = 1; //Increment Row Number
while($row = mysql_fetch_assoc($result)){
$time = $row['totalhours'];

echo "<tr align='left'>";    
echo"<td><font color='white'>" .$rowNo++."</font>.</td>";
echo"<td><font color='white'>" .$row['member_name']."</font>.</td>";
echo"<td><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_in']))."</font>.</td>";
echo"<td><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_out']))."</font>.</td>";
echo"<td><font color='white'>" .$time." Hrs</font>.</td>";            
echo "</tr>";                

}

 

the output is on the image. now how to i get total count of all rows as in total of all time_in and time_out?

Edited July 30, 2016 by lovephp

[Barand]

Or, you could just use the TIMEDIFF() function instead of those conversions

mysql> CREATE TEMPORARY TABLE temptimes (t_in DATETIME, t_out DATETIME);

mysql> INSERT INTO temptimes VALUES
    ->     ('2016-07-27 08:30:00', '2016-07-27 17:45:00'),
    ->     ('2016-07-27 19:30:00', '2016-07-28 07:45:00');

mysql> SELECT t_in, t_out, TIMEDIFF(t_out, t_in) as hrs FROM temptimes;
+---------------------+---------------------+----------+
| t_in                | t_out               | hrs      |
+---------------------+---------------------+----------+
| 2016-07-27 08:30:00 | 2016-07-27 17:45:00 | 09:15:00 |
| 2016-07-27 19:30:00 | 2016-07-28 07:45:00 | 12:15:00 |
+---------------------+---------------------+----------+

[lovephp]

 

Or, you could just use the TIMEDIFF() function instead of those conversions

mysql> CREATE TEMPORARY TABLE temptimes (t_in DATETIME, t_out DATETIME);

mysql> INSERT INTO temptimes VALUES
    ->     ('2016-07-27 08:30:00', '2016-07-27 17:45:00'),
    ->     ('2016-07-27 19:30:00', '2016-07-28 07:45:00');

mysql> SELECT t_in, t_out, TIMEDIFF(t_out, t_in) as hrs FROM temptimes;
+---------------------+---------------------+----------+
| t_in                | t_out               | hrs      |
+---------------------+---------------------+----------+
| 2016-07-27 08:30:00 | 2016-07-27 17:45:00 | 09:15:00 |
| 2016-07-27 19:30:00 | 2016-07-28 07:45:00 | 12:15:00 |
+---------------------+---------------------+----------+

thanks and how to get the total for both the rows? 21:30:00 hrs?

[Barand]

To use SUM() then you do need to convert

mysql> SELECT SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND,t_in,t_out))) as total 
   --> FROM temptimes;
+----------+
| total    |
+----------+
| 21:30:00 |
+----------+

[lovephp]

ok its solved. thank you both appreciate your help, cheers