Date functions and aggregate functions

Get Help
1

Hi there, I have a start time, end time (timecard), plus other info that the user has to enter each day in the app. Can I calculate the hour difference. I have looked and found many different javascript methods to do this. I am just not sure how to implement it. I am not sure if I should calculate on the fly each time the page is loaded or store the value thus increasing the size of the db. Secondly, i would need to calculate (aggregate) the hours worked. some pointers would be kind thank you.

Dave

2

Hey, David!

  1. If you store start time and end time in “HH:mm” format I suggest to create additional columns to store the timestamps in ISO format. This will help to easily calculate duration with moment.js library.
    If the data is saved to the ds by submitting the form, you can use the next code the generate timestamp in ISO format:
//save start timestamp in ISO format when user submits the form with start time
Fliplet.Hooks.on('beforeFormSubmit', function(data) {
  let startISO = moment().toISOString();
});

Be careful to generate one timestamp when the user saves start time and another when the user saves end time to the ds. Thus you’ll have 2 timestamps in the ds, e.g. startISO and endISO

  1. Next, when the user saves end time to the ds, it would be better to calculate duration and save it to the ds as well. You can use the next code as a base.
    Here you would need to convert your ISO timestamp into ‘YYYY-mm-dd HH:mm’ format using the same moment.js.
var startTime = '2016-02-21 18:00'; //convert here startISO with moment.js
var endTime = '2016-02-21 19:30';   //convert here endISO
var duration = moment.duration(moment(endTime, 'YYYY/MM/DD HH:mm').diff(moment(startTime, 'YYYY/MM/DD HH:mm'))).asHours();

I would not recommend storing duration in hours, as storing duration in munites or even secons is more accurate than storing hours.
And if you need to calculate total duration for some period of time, several hours or even days might be lost due to rounding.
It depends on your code, but if the user uses the form to submit start and end time, you can use ‘beforeFormSubmit’ hook to save end timestamp and duration.

Fliplet.Hooks.on('beforeFormSubmit', function(data) {
  let startTime = moment(startISO).format("YYYY-MM-DD HH:mm");
  let endISO = moment().toISOString();
  let endTime = moment(endISO).format("YYYY-MM-DD HH:mm");
  let duration = moment.duration(moment(endTime, 'YYYY/MM/DD HH:mm').diff(moment(startTime, 'YYYY/MM/DD HH:mm'))).asHours();
  data.endISO = endISO;
  data.duration = duration;
  retrun data;
});
3

Ok thanks for the reply. Sorry I did not clarify how the data is entered. These employees work in a bush setting. They are not worried about minutes or seconds. They will most likely select a time like 4:30am to start and an end time of 5:30pm. Of all the 1000+ timesheets i’ve seen, they are all like that.

4

Hey, David!
In this case you can store date and time separately as well as using ISO timestamp and store duration as decimal hours. Also, as I said, I would recommend calculating duration after submitting the end time and saving it to the ds, as calculating total durating will be much faster in this case and can be done with aggregation query.
Please, let us know if you have any questions.