0

I have two timestamps and I must find the "working hours" in between these two timestamps where "working hours" is defined as 9:00 to 17:00 (9AM to 5PM).

Given the timestamps 2022-04-25 15:00:00 and 2022-04-27 10:00:00:

2022-04-25 15:00:00 -> 2 hours

2022-04-26 -> 8 hours (full day)

2022-04-27 10:00:00 -> 1 hour

So the total would be 11 working hours.

I know how to do this in SQL with the EXTRACT function but I'm not as adept in JavaScript and my search hasn't returned anything useful. Alternatively, I could add up the time that falls in the "work hours" given two timestamps but I want to check if there's some pre-defined function for this.

SQL equivalent: SQL extract hour from timestamp

engineer-x
  • 2,173
  • 2
  • 12
  • 25
  • `new Date(timestampString).getHours()` will do the same as the SQL `EXTRACT( HOUR FROM timestampString::TIMESTAMP)` , but that doesn't really solve your problem with the working hours overlap? – Bergi Apr 26 '22 at 01:31

2 Answers2

0

Native API?

There is no easy API for this in JavaScript. You have to write such functionality yourself.

Possible solution

Here one approach to do that:

/**
 * Calculate working hours in a given time range. Does not take weekends into account.
 * @param {Date} start start Date of range
 * @param {Date} end end Date of range
 * @param {number} whStart start of working hours
 * @param {number} whEnd end of working hours
 * @returns object containing total working hours in range and working hours per day in milliseconds (ms) and hours (h)
 */
function workingHoursInRange(start, end, whStart, whEnd) {
  let totalTime = 0;
  // get day difference between start and end date
  const dayDiff = end.getDate() - start.getDate() + 1;
  let diff = 0;
  const workingHoursPerDay = [...Array(dayDiff)].map((_, i) => {
    // first day (start time till end of working hours)
    if (i === 0) diff = firstDay(start, whStart, whEnd);
    // last day (start of working hours till end date)
    else if (i === dayDiff - 1) diff = lastDay(end, whStart, whEnd);
    // days inbetween are full days
    else diff = (whEnd - whStart) * 1000 * 3600;
    totalTime += diff;
    return diff;
  });
  return {
    workingHoursPerDay: {
      ms: workingHoursPerDay,
      h: workingHoursPerDay.map((it) => it / (1000 * 3600)),
    },
    totalHours: {
      ms: totalTime,
      h: totalTime / (1000 * 3600),
    },
  };
}

/**
 * Calculate working hours on first day of a date range
 * @param {Date} date date
 * @param {number} whStart start of working hours
 * @param {number} whEnd end of working hours
 * @returns working hours in milliseconds
 */
function firstDay(date, whStart, whEnd) {
  const minEnd = new Date(
    date.getFullYear(),
    date.getMonth(),
    date.getDate(),
    whEnd
  );
  const maxStart = new Date(
    Math.max(
      new Date(date.getFullYear(), date.getMonth(), date.getDate(), whStart),
      date
    )
  );
  return minEnd - maxStart;
}

/**
 * Calculate working hours on last day of a date range
 * @param {Date} date date
 * @param {number} whStart start of working hours
 * @param {number} whEnd end of working hours
 * @returns working hours in milliseconds
 */
function lastDay(date, whStart, whEnd) {
  const maxStart = new Date(
    date.getFullYear(),
    date.getMonth(),
    date.getDate(),
    whStart
  );
  const minEnd = new Date(
    Math.min(
      new Date(date.getFullYear(), date.getMonth(), date.getDate(), whEnd),
      date
    )
  );
  return minEnd - maxStart;
}

// Test
const workingHoursStart = 9;
const workingHoursEnd = 17;
const input = [
  {
    start: new Date("2022-04-25 01:00:00"),
    end: new Date("2022-04-27 10:00:00"),
  },
  {
    start: new Date("2022-04-25 12:00:00"),
    end: new Date("2022-04-27 10:00:00"),
  },
  {
    start: new Date("2022-04-26 12:33:00"),
    end: new Date("2022-04-26 16:05:00"),
  },
];
// calculate working hours for all ranges
const result = input.map(({ start, end }) =>
  workingHoursInRange(start, end, workingHoursStart, workingHoursEnd)
);
// log result
console.log(result);
.as-console-wrapper { max-height: 100% !important; top: 0; }

Please note: currently this implementation does not take into account weekends but with some small adjustments this could be considered as well.

Essentially the idea is to split a date range into three parts:

  • first day
  • last day
  • days inbetween

The working hours on the first day will be determines by the maximum of the working hours start and the actual start date. For the last day the minimum of working hours end and end date will determine the total working hours on that day. For all days inbetween are full work days and we can just use the difference between working hours end and working hours start to calculate the total working hours.

This approach avoids possible caveats with date arithmetic such as daylight saving.

Mushroomator
  • 6,516
  • 1
  • 10
  • 27
0

So I don't know if there are any predefined functions or anything for this. You've said you've searched and didn't find a solution. So I assume there aren't any.

A fiddle

That's why I started fiddling! I liked the challenge. ;-) The code below does basically do what you want in the example I guess. It's more a proof of concept.

But that's also why there are still a few things that you need cover from here an out in a real use-case:

  • I didn't account for minutes for example
  • I didn't account for weekends
  • I didn't account for two timestamps that are between different months
    (from april 25 to may 2 for example)
  • Breaks?

But that's just some fine-tuning!!

function calcWorkedHours(start, end) {
  let workedHours = 0;
  let startDateTime = new Date(start);
  let endDateTime = new Date(end);
  let workDate = startDateTime.getDate();

  while (workDate <= endDateTime.getDate()) {
    if (workDate === startDateTime.getDate()) {
      // if they started before 17 oclock.
      if (startDateTime.getHours() < 17) {
        let workedHoursStartDate = 17 - startDateTime.getHours();
        console.log(workedHoursStartDate);
        workedHours += workedHoursStartDate;
      }
    } 
    else if (workDate === endDateTime.getDate()) {
      // if they finished after 9 oclock.
      if (startDateTime.getHours() > 9) {
        let workedHoursEndDate = endDateTime.getHours() - 9;
        console.log(workedHoursEndDate);
        workedHours += workedHoursEndDate;
      }
    } 
    else {
      let workedHoursFullDay = 8;
      console.log(workedHoursFullDay);
      workedHours += workedHoursFullDay;
    }

    workDate++;
  }

  console.log("Worked hours: " + workedHours);
  return workedHours;
}


calcWorkedHours("2022-04-25 15:00:00", "2022-04-27 10:00:00");
T-S
  • 707
  • 3
  • 8