Jump to content

best way to store login hours?


lovephp
Go to solution Solved by kicken,

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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'
  • Like 1
Link to comment
Share on other sites

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 by lovephp
Link to comment
Share on other sites

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 :D

Link to comment
Share on other sites

  • Solution

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
  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites


+--------------+ +----------------+ +--------------------+
| team | | member | | timeworked |
+--------------+ +----------------+ +--------------------+
| team_id (PK) |---+ | member_id (PK) |---+ | timeworked_id (PK) |
| teamname | +--<| team_id | +--<| member_id |
+--------------+ +----------------+ | timein |
| timeout |
+--------------------+
  • Like 1
Link to comment
Share on other sites

       +--------------+       +----------------+       +--------------------+                                             
       | 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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

post-132367-0-49816100-1469873700_thumb.jpg

Edited by lovephp
Link to comment
Share on other sites

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 |
+---------------------+---------------------+----------+

  • Like 1
Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.