SitePoint Sponsor

User Tag List

Page 1 of 4 1234 LastLast
Results 1 to 25 of 87
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Computing attendance hours

    Good day!

    I have 2 columns one for timein and one for timeout...and the data is from uploaded .xml file, my problem now is the saving of timeout in database..

    in my .xml file I have this data:

    Emp no Time In Time Out
    100603 10/1/11 7:30 AM 10/1/11 6:00 PM
    100603 10/2/11 8:00 AM 10/2/11 6:30 PM


    and it saves in db
    Emp no Time In Time Out
    100603 2011-10-01 07:30:00 2011-10-01 18:00:00
    100603 2011-10-02 08:00:00 2011-10-01 18:30:00

    I want the timein and time out is formatted in 12 hours. As you can see the time out is formatted in 24 hours.

    Thank you...

  2. #2
    SitePoint Evangelist
    Join Date
    Apr 2009
    Location
    South Carolina
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    Emp no Time In Time Out
    100603 10/1/11 7:30 AM 10/1/11 6:00 PM
    100603 10/2/11 8:00 AM 10/2/11 6:30 PM


    and it saves in db
    Emp no Time In Time Out
    100603 2011-10-01 07:30:00 2011-10-01 18:00:00
    100603 2011-10-02 08:00:00 2011-10-01 18:30:00

    I want the timein and time out is formatted in 12 hours. As you can see the time out is formatted in 24 hours.

    Thank you...
    Actually, both time in (07:30:00) and time out (18:00:00) are formatted in 24 hour format, and that is the way the database stores the information. Maybe if you explain what you are trying to accomplish, one of the members here can point you in the correct direction.
    Each day is a learning experience.

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by CSU-Bill View Post
    Actually, both time in (07:30:00) and time out (18:00:00) are formatted in 24 hour format, and that is the way the database stores the information. Maybe if you explain what you are trying to accomplish, one of the members here can point you in the correct direction.
    I upload .xml using php and it saves to database.

    I upload .xml using php and it saves to database.

    I tried this data to upload:
    100603 10/1/11 5:35 AM 10/1/11 1:35 PM // this is the exact schedule of employee and its 8 hours per day he time in exactly and timeout exactly also, so no problem in computing because it is 8 hours.
    100603 10/2/11 5:25 AM 10/2/11 1:55 PM //in this example data the employee time in early in his schedule and time out late.
    100603 10/3/11 5:40 AM 10/3/11 1:40 PM // in this example data the employee time in is late, so even he also late to timeout theres no exemption because he late on his work.

    and it saves it database:
    100603 2011-10-01 05:35:00 2011-10-01 13:35:00
    100603 2011-10-02 05:25:00 2011-10-01 13:55:00
    100603 2011-10-02 05:40:00 2011-10-01 13:40:00




    Now, I know that the data stored time in database formatted in 24 hours. Honestly, I want to accomplish is to get the total hours of the employee based on the employee no.

    and I tried this code:
    Code:
    select  sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein)) AS totalhours from employee;
    and the result of this code is:

    totalhours:
    08:00:00
    08:30:00
    08:00:00



    and the result is

    the first is correct because the real schedule is 5:35 AM - 1:35 PM
    the second is wrong it should be 8 hours only even he timein early and timeout late.
    the third is also wrong because the employee is late to timein, even he also timeout late., it should be deduct or subtract in hours the late of employee.

  4. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Computing attendance hours

    I upload attendance .xml using php and it saves to database.

    I tried this data to upload:
    100603 10/1/11 5:35 AM 10/1/11 1:35 PM // this is the exact schedule of employee and its 8 hours per day he time in exactly and timeout exactly also, so no problem in computing because it is 8 hours.
    100603 10/2/11 5:25 AM 10/2/11 1:55 PM //in this example data the employee time in early in his schedule and time out late. It should be only 8 hours.
    100603 10/3/11 5:40 AM 10/3/11 1:40 PM // in this example data the employee time in is late, so even he also late to timeout thereís no exemption because he late on his work. So it should has deduction or minus in his total hours.

    and it saves it database:
    100603 2011-10-01 05:35:00 2011-10-01 13:35:00
    100603 2011-10-02 05:25:00 2011-10-01 13:55:00
    100603 2011-10-02 05:40:00 2011-10-01 13:40:00


    I want to accomplish is to get the total hours of the employee based on the employee no. And even the employee get timein early before his time or late to timeout the hours computed only 8 hours. Honestly, I donít have idea how can be possible it is.

    and I tried this code for computing the hours per day:
    Code:
    select  sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein)) AS totalhours from employee;
    and the result of this code is:

    totalhours:
    08:00:00
    08:30:00
    08:00:00



    and the result is

    the first is correct because the real schedule is 5:35 AM - 1:35 PM
    the second is wrong it should be 8 hours only even he timein early and timeout late.
    the third is also wrong because the employee is late to timein, even he also timeout late., it should be deduct or subtract in hours the late of employee.

    I hope someone can help me. Thank you

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,406
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    What you need to do is confront the registered timein time with the regular timein time, and the same with the timeout time.

    If the employee times in before the regular timein time, the difference is zero.
    If the employee times in too late, you calculate the difference (in seconds).

    If the employee times out after the regular timeout time, the difference is zero.
    If the employee times out too early, you calculate the difference (in seconds).

    Then you subtract the differences from the 8 hours.

    Maybe it would be easier to do it in PHP, but in MySQL you could try something like this (I didn't test it, I have no idea if you can use CASE's like that in a time function):
    Code:
    sec_to_time(time_to_sec('08:00:00') + 
                case 
                  when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0
                  else time_to_sec('05:35:00') - time_to_sec(time(timein))
                end +
                case 
                  when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0
                  else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
                end
               )
    Don't let the '+' signs confuse you, I wrote the calculations in a way that the result will be 0 or negative, so adding the negative difference will make sure it will be subtracted from the 8 hours.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I think your question is about who does what between PHP and mysql ... this is my take:

    Clearly for each employee you need to extract things which permit you to work out:

    a) id
    b) total hours worked
    c) whether they were late or not

    Get mysql to collect up the numbers, get PHP to work out the details.

    So your result set wants to be something like:
    PHP Code:
    $row[0] = array(
    'EmpID' => '10023',
    'total_hrs' => '08:00:00',
    'start_time' => '05:40:00',
    ); 
    in pseudocode that would result in being processed with something like:

    Code:
    if( $late === false && $total_hrs > 8 ) 
       // pay the man
    
    if($late === true )
       // work out penalty of lateness
       // see if they went home early - find that penalty 
       // add them together, and apply
    Does that give you a leg up?

    You could get mysql to return you a flag for "late" with some effort, but that would entail putting that logic into your database queries, when I suspect that start_time is could be quite a variable thing, as could the length of a working day, the insertion of lunch breaks etc.

    These things are best left to PHP to perform, at least when starting out.

    EDIT

    Cripes, took me that long to create a reply! ... well, at least you have 2 different views now ...

  7. #7
    SitePoint Evangelist
    Join Date
    Apr 2009
    Location
    South Carolina
    Posts
    458
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The results are correct as required by your code. If you want to deduct time due to non-allowed clock-in and clock-out times, you need to have code that makes that correction.

    I don't know where you are located, and I am not a lawyer, but I would suggest you check with your legal adviser to be sure you are allowed to make those changes to the employees times.
    Each day is a learning experience.

  8. #8
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good day!

    I have new sample for further understanding of my problem.

    The real schedule of employee DS-1001 is from 9:35 PM to 5:35 AM he is night shift and the employee DS-1002 is from 5:35 AM to 1:35 PM he is morning shift.

    here is the sample .xml file
    EMP_NO Time In Time Out
    DS-1001 10/1/11 9:35 PM 10/2/11 5:35 AM // this is the exact timein and timeout so theres no problem
    DS-1001 10/2/11 9:00 PM 10/3/11 6:00 AM // in this sample the employee timein early and also timeout late.
    DS-1001 10/3/11 10:00 PM 10/4/11 5:00 AM // in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,
    DS-1002 10/1/11 5:35 AM 10/1/11 1:35 PM// this is the exact timein and timeout so theres no problem
    DS-1002 10/2/11 5:00 AM 10/2/11 2:00 PM// in this sample the employee timein early and also timeout late.
    DS-1002 10/3/11 6:00 AM 10/3/11 1:00 PM// in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,


    and the data save in database is:
    EMP_NO timein timeout total rendered
    DS-1001 2011-10-01 21:35:00 2011-10-02 05:35:00 00:00:00 00:00:00
    DS-1001 2011-10-02 21:00:00 2011-10-03 06:00:00 00:00:00 00:00:00
    DS-1001 2011-10-03 22:00:00 2011-10-04 05:00:00 00:00:00 00:00:00
    DS-1002 2011-10-01 05:35:00 2011-10-01 13:35:00 00:00:00 00:00:00
    DS-1002 2011-10-02 05:00:00 2011-10-02 14:00:00 00:00:00 00:00:00
    DS-1002 2011-10-03 06:00:00 2011-10-03 13:00:00 00:00:00 00:00:00

    OT
    00:00:00
    00:00:00
    00:00:00
    00:00:00
    00:00:00
    00:00:00

    EMP_NO datatype is varchar
    timein datatype is datetime
    timeout datatype is datetime
    total datatype is time
    rendered datatype is time
    OT datatype is time

    the total field is the sum of the total hours of the employee
    the rendered field is the exact 8 hours of employee or if the employee late like for example he is late or timeout early it should be subtracted and be output in rendered but normally it is 8 hours if his not late. In this field I don't have idea how can i do that.
    the OT field is the total - rendered field.

    I dont know how can I insert that in my database.



    I used this code to get the total hours but it did not work, and no error displayed.
    Code:
    INSERT INTO employee (total) SELECT sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));
    I hope somebody can help me...

    Thank you so much...

  9. #9
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good day!

    I have new sample for further understanding of my problem.

    The real schedule of employee DS-1001 is from 9:35 PM to 5:35 AM he is night shift and the employee DS-1002 is from 5:35 AM to 1:35 PM he is morning shift.

    here is the sample .xml file
    EMP_NO Time In Time Out
    DS-1001 10/1/11 9:35 PM 10/2/11 5:35 AM // this is the exact timein and timeout so theres no problem
    DS-1001 10/2/11 9:00 PM 10/3/11 6:00 AM // in this sample the employee timein early and also timeout late.
    DS-1001 10/3/11 10:00 PM 10/4/11 5:00 AM // in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,
    DS-1002 10/1/11 5:35 AM 10/1/11 1:35 PM// this is the exact timein and timeout so theres no problem
    DS-1002 10/2/11 5:00 AM 10/2/11 2:00 PM// in this sample the employee timein early and also timeout late.
    DS-1002 10/3/11 6:00 AM 10/3/11 1:00 PM// in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,


    and the data save in database is:
    EMP_NO timein timeout total rendered
    DS-1001 2011-10-01 21:35:00 2011-10-02 05:35:00 00:00:00 00:00:00
    DS-1001 2011-10-02 21:00:00 2011-10-03 06:00:00 00:00:00 00:00:00
    DS-1001 2011-10-03 22:00:00 2011-10-04 05:00:00 00:00:00 00:00:00
    DS-1002 2011-10-01 05:35:00 2011-10-01 13:35:00 00:00:00 00:00:00
    DS-1002 2011-10-02 05:00:00 2011-10-02 14:00:00 00:00:00 00:00:00
    DS-1002 2011-10-03 06:00:00 2011-10-03 13:00:00 00:00:00 00:00:00

    OT
    00:00:00
    00:00:00
    00:00:00
    00:00:00
    00:00:00
    00:00:00

    EMP_NO datatype is varchar
    timein datatype is datetime
    timeout datatype is datetime
    total datatype is time
    rendered datatype is time
    OT datatype is time

    the total field is the sum of the total hours of the employee
    the rendered field is the exact 8 hours of employee or if the employee late like for example he is late or timeout early it should be subtracted and be output in rendered but normally it is 8 hours if his not late. In this field I don't have idea how can i do that.
    the OT field is the total - rendered field.

    I dont know how can I insert that in my database.



    I used this code to get the total hours but it did not work, and no error displayed.
    Code:
    INSERT INTO employee (total) SELECT sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));
    I hope somebody can help me...

    Thank you so much...

  10. #10
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tis is the vardump of my database:
    Code:
    -- MySQL Administrator dump 1.4
    --
    -- ------------------------------------------------------
    -- Server version	5.1.41
    
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    
    --
    -- Create schema db_upload
    --
    
    CREATE DATABASE IF NOT EXISTS db_upload;
    USE db_upload;
    
    --
    -- Definition of table `db_upload`.`employee`
    --
    
    DROP TABLE IF EXISTS `db_upload`.`employee`;
    CREATE TABLE  `db_upload`.`employee` (
      `EMP_NO` varchar(50) NOT NULL,
      `timein` datetime NOT NULL,
      `timeout` datetime NOT NULL,
      `total` time NOT NULL,
      `rendered` time NOT NULL,
      `OT` time NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `db_upload`.`employee`
    --
    
    /*!40000 ALTER TABLE `employee` DISABLE KEYS */;
    INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`total`,`rendered`,`OT`) VALUES 
     ('DS-1001','2011-10-01 21:35:00','2011-10-02 05:35:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1001','2011-10-02 21:00:00','2011-10-03 06:00:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1001','2011-10-03 22:00:00','2011-10-04 05:00:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1002','2011-10-01 05:35:00','2011-10-01 13:35:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1002','2011-10-02 05:00:00','2011-10-02 14:00:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1002','2011-10-03 06:00:00','2011-10-03 13:00:00','00:00:00','00:00:00','00:00:00');
    /*!40000 ALTER TABLE `employee` ENABLE KEYS */;
    
    
    
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

  11. #11
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tis is the vardump of my database:
    Code:
    -- MySQL Administrator dump 1.4
    --
    -- ------------------------------------------------------
    -- Server version	5.1.41
    
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    
    --
    -- Create schema db_upload
    --
    
    CREATE DATABASE IF NOT EXISTS db_upload;
    USE db_upload;
    
    --
    -- Definition of table `db_upload`.`employee`
    --
    
    DROP TABLE IF EXISTS `db_upload`.`employee`;
    CREATE TABLE  `db_upload`.`employee` (
      `EMP_NO` varchar(50) NOT NULL,
      `timein` datetime NOT NULL,
      `timeout` datetime NOT NULL,
      `total` time NOT NULL,
      `rendered` time NOT NULL,
      `OT` time NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `db_upload`.`employee`
    --
    
    /*!40000 ALTER TABLE `employee` DISABLE KEYS */;
    INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`total`,`rendered`,`OT`) VALUES 
     ('DS-1001','2011-10-01 21:35:00','2011-10-02 05:35:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1001','2011-10-02 21:00:00','2011-10-03 06:00:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1001','2011-10-03 22:00:00','2011-10-04 05:00:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1002','2011-10-01 05:35:00','2011-10-01 13:35:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1002','2011-10-02 05:00:00','2011-10-02 14:00:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1002','2011-10-03 06:00:00','2011-10-03 13:00:00','00:00:00','00:00:00','00:00:00');
    /*!40000 ALTER TABLE `employee` ENABLE KEYS */;
    
    
    
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

  12. #12
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tis is the vardump of my database:
    Code:
    -- MySQL Administrator dump 1.4
    --
    -- ------------------------------------------------------
    -- Server version	5.1.41
    
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    
    --
    -- Create schema db_upload
    --
    
    CREATE DATABASE IF NOT EXISTS db_upload;
    USE db_upload;
    
    --
    -- Definition of table `db_upload`.`employee`
    --
    
    DROP TABLE IF EXISTS `db_upload`.`employee`;
    CREATE TABLE  `db_upload`.`employee` (
      `EMP_NO` varchar(50) NOT NULL,
      `timein` datetime NOT NULL,
      `timeout` datetime NOT NULL,
      `total` time NOT NULL,
      `rendered` time NOT NULL,
      `OT` time NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `db_upload`.`employee`
    --
    
    /*!40000 ALTER TABLE `employee` DISABLE KEYS */;
    INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`total`,`rendered`,`OT`) VALUES 
     ('DS-1001','2011-10-01 21:35:00','2011-10-02 05:35:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1001','2011-10-02 21:00:00','2011-10-03 06:00:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1001','2011-10-03 22:00:00','2011-10-04 05:00:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1002','2011-10-01 05:35:00','2011-10-01 13:35:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1002','2011-10-02 05:00:00','2011-10-02 14:00:00','00:00:00','00:00:00','00:00:00'),
     ('DS-1002','2011-10-03 06:00:00','2011-10-03 13:00:00','00:00:00','00:00:00','00:00:00');
    /*!40000 ALTER TABLE `employee` ENABLE KEYS */;
    
    
    
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

  13. #13
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,909
    Mentioned
    96 Post(s)
    Tagged
    0 Thread(s)
    Two threads merged. Please don't create a second thread for a problem when you already have one open (thread moved to MySQL forum)

    I think your problem should be solvable with MySQL, I'll give it go when I get home
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  14. #14
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'mSorry....Thank you...I will test the query and i will tell you if my query run..I think my problem is on the rendered fields. Thank you so much

  15. #15
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I change my syntax from insert to update because the total field will only update because they are in same table of timein and timeout, so I used this syntax and i test it in mysql:

    Code:
    UPDATE employee
    SET total= sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));
    When I used this syntax, the output was add in total field and it is correct...Now my problem is in the rendered, i dont know how can I display the rendered time the 8 hours or below 8 hours if he is late or early to timeout.Because now in total i only used update query and i run it in mysql.

    In rendered it only gets the 8 hours from like for example 9:35 PM to 5:35 AM if he timein 9:00 PM he only get from 9:35PM - 5:35 AM or if he timeout 5:40 AM only the 9:35 PM - 5:35 AM he sum and insert in rendered field.

    Thank you so much...

  16. #16
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much...

  17. #17
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    What you need to do is confront the registered timein time with the regular timein time, and the same with the timeout time.

    If the employee times in before the regular timein time, the difference is zero.
    If the employee times in too late, you calculate the difference (in seconds).

    If the employee times out after the regular timeout time, the difference is zero.
    If the employee times out too early, you calculate the difference (in seconds).

    Then you subtract the differences from the 8 hours.

    Maybe it would be easier to do it in PHP, but in MySQL you could try something like this (I didn't test it, I have no idea if you can use CASE's like that in a time function):
    Code:
    sec_to_time(time_to_sec('08:00:00') + 
                case 
                  when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0
                  else time_to_sec('05:35:00') - time_to_sec(time(timein))
                end +
                case 
                  when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0
                  else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
                end
               )
    Don't let the '+' signs confuse you, I wrote the calculations in a way that the result will be 0 or negative, so adding the negative difference will make sure it will be subtracted from the 8 hours.


    I have 3 shifts 21:35:00 to 05:35:00 , 05:35:00 to 13:35:00 , and 13:35:00 to 21:35:00

    I have this data in my database:
    EMP_NO| timein| timeout |total| rendered
    DS-1001 2011-10-01 21:35:00 2011-10-02 05:35:00 08:00:00 00:00:00
    DS-1001 2011-10-02 21:00:00 2011-10-03 06:00:00 09:00:00 00:00:00
    DS-1001 2011-10-03 22:00:00 2011-10-04 05:00:00 07:00:00 00:00:00
    DS-1002 2011-10-01 05:35:00 2011-10-01 13:35:00 08:00:00 00:00:00
    DS-1002 2011-10-02 05:00:00 2011-10-02 14:00:00 09:00:00 00:00:00
    DS-1002 2011-10-03 06:00:00 2011-10-03 13:00:00 07:00:00 00:00:00

    OT
    00:00:00
    00:00:00
    00:00:00
    00:00:00
    00:00:00
    00:00:00

    I compute my total buy this code:
    Code:
    UPDATE employee
    SET total= sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));
    I test the code you suggesed and I add syntax for 21:35:00 - 05:35:00 shift:
    Code:
    select sec_to_time(time_to_sec('08:00:00') + 
                case 
                  when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0
                  else time_to_sec('05:35:00') - time_to_sec(time(timein))
                end +
                case 
                  when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0
                  else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
                end+
    	    case 
                  when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0
                  else time_to_sec('21:35:00') - time_to_sec(time(timein))
                end +
                case 
                  when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0
                  else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
                end
               ) FROM employee;
    and the output is
    -16:00:00
    -15:00:00
    -18:00:00
    08:00:00
    08:00:00
    07:00:00

    I'm hoping that this output will insert in rendered field, I see only a problem in night shift 21:35:00 - 05:35:00
    Thank you so much

  18. #18
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,909
    Mentioned
    96 Post(s)
    Tagged
    0 Thread(s)
    Does this query give you the correct time worked by the employees'?

    Code SQL:
    SELECT
          employee.EMP_NO
        , TIMESTAMPDIFF(HOUR,employee.timein,employee.timeout) AS worked_hours
        , ( minutes_worked.time_worked_minutes-(time_worked_hours*60)) AS worked_minutes
        , employee.timein
        , employee.timeout
        , total
        , rendered
        , OT
    FROM
        (
            SELECT
                  EMP_NO
                , TIMESTAMPDIFF(MINUTE,employee.timein,employee.timeout) AS time_worked_minutes
            FROM
                employee
        ) AS minutes_worked
    INNER JOIN
        employee
            ON employee.EMP_NO=minutes_worked.EMP_NO
    INNER JOIN        
        (
            SELECT
                  EMP_NO
                , TIMESTAMPDIFF(HOUR,employee.timein,employee.timeout) AS time_worked_hours
            FROM
                employee
        ) AS hours_worked
            ON minutes_worked.EMP_NO=hours_worked.EMP_NO
    GROUP BY
        employee.timein

    Note that the TIMESTAMPDIFF() function may not be available on all servers (ie MSSQL, Oracle, etc) but there may an equivilant function available.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  19. #19
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Does this query give you the correct time worked by the employees'?

    Code SQL:
    SELECT
          employee.EMP_NO
        , TIMESTAMPDIFF(HOUR,employee.timein,employee.timeout) AS worked_hours
        , ( minutes_worked.time_worked_minutes-(time_worked_hours*60)) AS worked_minutes
        , employee.timein
        , employee.timeout
        , total
        , rendered
        , OT
    FROM
        (
            SELECT
                  EMP_NO
                , TIMESTAMPDIFF(MINUTE,employee.timein,employee.timeout) AS time_worked_minutes
            FROM
                employee
        ) AS minutes_worked
    INNER JOIN
        employee
            ON employee.EMP_NO=minutes_worked.EMP_NO
    INNER JOIN        
        (
            SELECT
                  EMP_NO
                , TIMESTAMPDIFF(HOUR,employee.timein,employee.timeout) AS time_worked_hours
            FROM
                employee
        ) AS hours_worked
            ON minutes_worked.EMP_NO=hours_worked.EMP_NO
    GROUP BY
        employee.timein

    Note that the TIMESTAMPDIFF() function may not be available on all servers (ie MSSQL, Oracle, etc) but there may an equivilant function available.
    When I tried this code the data in my database goes like this:

    vardump of my db;
    Code:
    -- MySQL Administrator dump 1.4
    --
    -- ------------------------------------------------------
    -- Server version	5.1.41
    
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    
    --
    -- Create schema db_upload
    --
    
    CREATE DATABASE IF NOT EXISTS db_upload;
    USE db_upload;
    
    --
    -- Definition of table `db_upload`.`employee`
    --
    
    DROP TABLE IF EXISTS `db_upload`.`employee`;
    CREATE TABLE  `db_upload`.`employee` (
      `EMP_NO` varchar(50) NOT NULL,
      `timein` datetime NOT NULL,
      `timeout` datetime NOT NULL,
      `total` time NOT NULL,
      `rendered` time NOT NULL,
      `OT` time NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `db_upload`.`employee`
    --
    
    /*!40000 ALTER TABLE `employee` DISABLE KEYS */;
    INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`total`,`rendered`,`OT`) VALUES 
     ('DS-1001','2011-10-01 21:35:00','2011-10-02 05:35:00','08:00:00','08:00:00','00:00:00'),
     ('DS-1001','2011-10-02 21:00:00','2011-10-03 06:00:00','09:00:00','08:00:00','01:00:00'),
     ('DS-1001','2011-10-03 22:00:00','2011-10-04 05:00:00','07:00:00','07:00:00','00:00:00'),
     ('DS-1002','2011-10-01 05:35:00','2011-10-01 13:35:00','08:00:00','08:00:00','00:00:00'),
     ('DS-1002','2011-10-02 05:00:00','2011-10-02 14:00:00','09:00:00','08:00:00','01:00:00'),
     ('DS-1002','2011-10-03 06:00:00','2011-10-03 13:00:00','07:00:00','08:00:00','-01:00:00'),
     ('DS-1003','2011-10-01 13:35:00','2011-10-01 21:35:00','08:00:00','08:00:00','00:00:00'),
     ('DS-1003','2011-10-02 13:00:00','2011-10-02 22:00:00','09:00:00','08:00:00','01:00:00'),
     ('DS-1003','2011-10-03 14:00:00','2011-10-03 21:00:00','07:00:00','08:00:00','-01:00:00');
    /*!40000 ALTER TABLE `employee` ENABLE KEYS */;
    
    
    
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

  20. #20
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have this query to compute the total, rendered, OT
    I used this query to insert the total, rendered and OT

    Code:
    ---Getting total----
    UPDATE employee SET total = sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));
    
    ---GEtting Rendered----
    UPDATE employee SET rendered = sec_to_time(time_to_sec('08:00:00') + 
                case 
                  when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0
                  else time_to_sec('21:35:00') - time_to_sec(time(timein))
                end +
                case 
                  when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0
                  else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
                end);
    
    ----GEtting OT-----
    UPDATE employee SET total = sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));
    Is there a way to correct my syntax in rendered so that I could compute my OT correct...Thank you

  21. #21
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any snytax/query except case statement if impossible to get the rendered from timein and timeout correctly?
    The case statement that I post is produce correct data in oly one shift, the rest is wrong.

    Thank you

  22. #22
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this query:
    Code:
    UPDATE employee SET rendered = sec_to_time(time_to_sec('08:00:00') + 
               case 
                  when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0
                  else time_to_sec('21:35:00') - time_to_sec(time(timein))
    	      when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0
                  else time_to_sec('05:35:00') - time_to_sec(time(timein))
    	      when time_to_sec(time(timein)) < time_to_sec('13:35:00') then 0
                  else time_to_sec('13:35:00') - time_to_sec(time(timein))
                end +
                case 
                  when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0
                  else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
                  when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0
                  else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
                  when time_to_sec(time(timeout)) > time_to_sec('21:35:00') then 0
                  else time_to_sec(time(timeout)) - time_to_sec('21:35:00')
                end)
    ;
    and i got this error:
    Error Code : 1064
    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 'when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0

    e' at line 9
    (0 ms taken)

  23. #23
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,406
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    and i got this error:
    Error Code : 1064
    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 'when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0

    e' at line 9
    (0 ms taken)
    You can't have multiple ELSE statements in a CASE. You can have multiple WHEN's, and only one ELSE (has to be the last one in the CASE).


    By the way, since you have three shifts, how do you decide against which shift you have to confront the logged in and out times of the employee?

  24. #24
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    You can't have multiple ELSE statements in a CASE. You can have multiple WHEN's, and only one ELSE (has to be the last one in the CASE).


    By the way, since you have three shifts, how do you decide against which shift you have to confront the logged in and out times of the employee?
    I only based on timein and timeout...Is it possible in case statement?How can I revise my case statement?
    Thank you so much....I really need this to solve...Thank you

  25. #25
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any other syntax to solve my problem?or should i edit my case statement, but i am new in case statement..Thank you..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •