How to keep the date from a datetime column in MySQL and modify the time only?

SEI-Nikki

Description of problem: I am creating a timeclock system for our employees. They are able to go back to their weekly clock in and out records and change the clock in and out times of the previous records.

There is error checking done to ensure their times do not conflict with other records of that one particular date. For example, if there is a record on a Monday from 7AM - 12:00PM and the next is 12:30PM-3:30PM, the second record could not have a clock in time changed to 11:30AM.

These times must be validated by the original date of the record. I have three columns for each record that are relevant - a date column for the date of the record, a timestamp clock in time, and a datetime clock out time. The issues I have found are as follows:

First rendition: I tried to use just time on the two clock columns, but when submitting a change, the date portion of the datetime column updated to today's date instead of staying the same date with the new time. This is an issue because it changed the table to having all modified records be a part of today's time clock records, leaving the error checking to return a consistent response of "Dates must be the same". When I tried to use the error checking stored procedure to verify the difference in dates, there was no consistency as to when the date field would also update to today's date. I tried to disable the date column to ensure this would not change, but that leaves the user with the clunkiness of having to enter the date of the clock in and out columns twice.

Second rendition: I changed the datetime fields to being both date and time, which is redundant because these records have to be in the same day but the day is rarely today's date. To try to mend this, I tried to use SET_EDIT on the serverside, but could not figure out how to default the values to the original date (and perhaps I missed something and could use the date column?).

Current code (as seen in the images):

Fields from Editor:

fields: [ 
      {
        label: "Date:",
        name: "clock_in.clock_date",
        type: "datetime",
        format: "MMMM D, YYYY",
        wireFormat: "YYYY-MM-DD"
      },
      {
        label: 'Job:',
        name:  'clock_in.job_no',
        type: "select"
      }, {
        label: 'In:',
        name:  'clock_in.clock_in_time',
        type: "datetime",
        format: "MMMM D, YYYY h:mm A",
        wireFormat: "YYYY-MM-DD HH:mm:ss"
      }, {
        label:  'Out:',
        name:   'clock_in.clock_out_time',
        type:   'datetime',
        format: "MMMM D, YYYY h:mm A",
        wireFormat: "YYYY-MM-DD HH:mm:ss"
      }
      ]

Error Checking via Editor - this runs a stored procedure to return a validation code so records do not overlap:

 editor.on("preSubmit", function(e, o, action) {
      if (submitFlag === false) {
        //if (action == 'edit') {
          //var rowid = data.DT_RowId;
          rowid = this.ids()[0];
          rowdate = this.field('clock_in.clock_date').val();
          newin = this.field('clock_in.clock_in_time').val();
          newout = this.field('clock_in.clock_out_time').val();
          newjob = this.field('clock_in.job_no').val();
          responseValidation(rowid, rowdate, newin, newout, newjob);
          return false;
        }
        submitFlag = false;
      });
      
      function responseValidation(rowid, rowdate, cin, cout, job) {
        $.ajax({
          url: "/timeclock/include/updateRecord.php",
          type: "POST",
          data: {
            rowid: rowid,
            rowdate: rowdate,
            newin: newin,
            newout: newout,
            newjob: newjob
          },
          success: function(response) {
            if (response == "1") {
              editor.field('clock_in.clock_out_time').error("Clock in time must be less than the clock out time.");
              submitFlag = false;
            } else if (response == "2") {
              editor.field('clock_in.clock_in_time').error("Clock in time conflicts with another record.");
              submitFlag = false;
            } else if (response == "3") {
              editor.field('clock_in.clock_out_time').error("Clock out time conflicts with another record.");
              submitFlag = false;
            } else if (response == "4") {
              editor.field('clock_in.clock_in_time').error("Clock in time conflicts with another record.");
              editor.field('clock_in.clock_out_time').error("Clock out time conflicts with another record.");
              submitFlag = false;
            } else if (response == "5") {
              editor.field('clock_in.clock_out_time').error("Time clock records must occur within the same day.");
              submitFlag = false;
            } else if (response == "6") {
              editor.field('clock_in.clock_in_time').error("Clock in time is required.");
              submitFlag = false;
            } else if (response == "7") {
              editor.field('clock_in.clock_out_time').error("Clock out time is required.");
              submitFlag = false;
            } else {
              submitFlag = true;
              editor.submit();
            }
          }
        });
        
      }

Table column definitions:

 columns: [
          { data: "clock_in.clock_date",
            render: function(data, type, row) {
              return moment(data).format("MMMM D, YYYY");
            } },
            { data: "001_PROJTRK.projtrk_bid_no", editField: "clock_in.job_no" },
            { data: "clock_in.clock_in_time", 
              render: function(data, type, row) {
                return moment(data).format("h:mm A");
              } },
              { data: "clock_in.clock_out_time", 
                render: function(data, type, row) {
                  return moment(data).format("h:mm A");
                } },
                { data: "clock_in.time_worked" }
              ],

Controller:

->field( 
    Field::inst( 'clock_in.clock_date' )
    ->validator( Validate::dateFormat(
        'Y-m-d',
        ValidateOptions::inst()
            ->allowEmpty( false )
    ) )
    ->getFormatter( Format::datetime( 'Y-m-d', 'Y-m-d' ) )
    ->setFormatter( Format::datetime( 'Y-m-d', 'Y-m-d' ) ),
    Field::inst( 'clock_in.clock_in_time' )
    ->validator( Validate::dateFormat(
        'Y-m-d H:i:s',
        ValidateOptions::inst()
            ->allowEmpty( false )
    ) )
    ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
    ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),
    Field::inst( 'clock_in.clock_out_time' )
    ->validator( Validate::dateFormat(
        'Y-m-d H:i:s',
        ValidateOptions::inst()
            ->allowEmpty( false )
    ) )
    ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
    ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),

Related question to this table: Is there a way to use RowGrouping without actually showing the column? For instance, instead of having a column in the table for the date, simply group by the date and order by the time columns?

allan

Good question - this is a slightly tricky one! A question in return - can the clock out date be on a different day (e.g. say a shift starts at 8pm on Saturday and finishes at 4am on Sunday)? An extension to that is can the second shift start on a different day?

If not, then the approach I would take is to have a single date input and three time inputs. Then use a custom set formatter on the server-side to combine the date and time fields into something to set for the database (the set formatter has access to all of the fields submitted, so this will probably just be a case of concatenation).

If however the answer to my question is that yes, the days can be different, I don't really see anyway other than to present them with a full date time picker for all inputs and add a validator to make sure that the start to end isn't over a 24 hour period.

Regards,
Allan

SEI-Nikki

Allan,
Thank you for your response! The dates must be on the same day, so I used the custom set formatter you linked to concat the date field with the two time fields and it worked after a little bit of tweaking. I hadn't considered other ways to use that feature beyond non-checkbox type fields but it does exactly what I needed it to.

Thanks again!

sinfuljosh

As for the roe grouping yes you can hide the datatables columns,

You add visible: false to that column.
And you can still reference that data for the group datasrc

https://datatables.net/reference/option/rowGroup.dataSrc