How to Get the Difference Between Two Dates Rounded to Hours

How to get the difference between two dates rounded to hours

select time_to_sec(timediff('2010-09-01 03:00:00', '2010-09-01 00:10:00' )) / 3600;

+-----------------------------------------------------------------------------+
| time_to_sec(timediff('2010-09-01 03:00:00', '2010-09-01 00:10:00' )) / 3600 |
+-----------------------------------------------------------------------------+
|                                                                      2.8333 | 
+-----------------------------------------------------------------------------+

Difference between dates, rounded result to nearest minute

It looks like you want a function that subtracts one date from another and round the difference to the nearest minute.

This can't be done with a single argument like the round function in your question; you need a function that takes both dates as arguments.

I'm not too familiar with moment, but here's a function that should work for built-in Dates.

EDIT: Per OP's comments, I've updated the function to modify d2 rather than returning diff.

    MS_IN_MINUTES = 60000;

    roundAway = (d1, d2) => {

      let diff = d2 - d1 // Difference in milliseconds (+ or -)

      let positive = diff > 0 // Whether we should add later

      diff = Math.abs(diff) // Difference in milliseconds (+)

      diff = diff / MS_IN_MINUTES // Difference in minutes (not rounded)

      diff = Math.ceil(diff) // Difference in minutes (rounded up)

      let roundedAway = d1.getTime()

      if (positive) {

          roundedAway += diff * MS_IN_MINUTES

      }

      else {

          roundedAway -= diff * MS_IN_MINUTES

      }

      d2.setTime(roundedAway)

    }

    

    let date1 = new Date("2019-06-29 21:25:38+00");

    console.log('date1:', date1)

    let date2 = new Date("2019-06-29T21:25:40.000+00:00");

    console.log('date2:', date2)

    let date3 = new Date("2019-06-29T21:24:36.000+00:00");

    console.log('date3:', date3)

    console.log('Unchanged date1:', date1);

    roundAway(date1, date2);

    console.log('Rounded date2:', date2);

    roundAway(date1, date3);

    console.log('Rounded date3:', date3);

How do I find the hour difference between two dates in PHP?

You can convert them to timestamps and go from there:

$hourdiff = round((strtotime($time1) - strtotime($time2))/3600, 1);

Dividing by 3600 because there are 3600 seconds in one hour and using round() to avoid having a lot of decimal places.

How can I get the difference in hours between two dates?

The error is because SYSDATE is already a date, there's no need to use TO_DATE() to convert it to a date.

If you don't convert it to a date:

select
    24 * (sysdate - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;

And if the formatting of the dates are wrong, you can possible use two steps like:

select
    24 * (to_date(to_char(sysdate, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;

Find the difference between two dates in hours and minutes

I've shown a couple of variations with explanations in this answer, but it seems to be doing slightly more than you want - you don't want to see the seconds - and doesn't allow more than 100 hours.

The simplest way to get the output you want is with:

trunc(24 * (RM_LIVE.CRWGNDACTTIME.GNDACTSTART
    - RM_LIVE.TRANSACTIONLOG.TIMESTAMP))
  ||':'|| lpad(round(60 * mod(24 * (RM_LIVE.CRWGNDACTTIME.GNDACTSTART
    - RM_LIVE.TRANSACTIONLOG.TIMESTAMP), 1)), 2, '0')
as difference

The first part gets the whole number of hours, which is similar to a method you added in a comment, but truncating instead of rounding to only get the whole hours. Then there's a colon separator. Then the minutes are calculated by getting the remainder from the hours calculation - via mod() - which is the fractional number of hours, and multiplying that by 60. The lpad() adds a leading zero to the number of minutes, but you coudl use to_char() instead.

If you have a mix of ranges where timestamp could be before or after the start time then you can use the abs() function to always get a positive result.

trunc(24 * abs(RM_LIVE.CRWGNDACTTIME.GNDACTSTART
    - RM_LIVE.TRANSACTIONLOG.TIMESTAMP))
  ||':'|| lpad(round(60 * mod(24 * abs(RM_LIVE.CRWGNDACTTIME.GNDACTSTART
    - RM_LIVE.TRANSACTIONLOG.TIMESTAMP), 1)), 2, '0')
as difference

As a demo with your data mocked up in a single table:

create table your_table(id, start_time, timestamp) as
  select 1, to_date ('05/JAN/2016 05:30:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('01/JAN/2016 10:02:29', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 2, to_date ('30/JAN/2016 06:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('18/JAN/2016 19:24:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 3, to_date ('23/JAN/2016 06:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('08/JAN/2016 10:46:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 4, to_date ('05/JAN/2016 05:30:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('30/DEC/2015 16:07:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 5, to_date ('23/JAN/2016 06:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('08/JAN/2016 12:18:05', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 6, to_date ('01/JAN/2016 14:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('16/DEC/2015 16:36:56', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 7, to_date ('01/JAN/2016 14:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('16/DEC/2015 11:41:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 8, to_date ('03/JAN/2016 05:15:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('02/JAN/2016 11:23:15', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 9, to_date ('03/JAN/2016 05:15:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('02/JAN/2016 07:52:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 10, to_date ('16/JAN/2016 11:15:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('16/JAN/2016 12:44:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 11, to_date ('16/JAN/2016 11:15:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('16/JAN/2016 12:50:00', 'DD/MON/YYYY HH24:MI:SS') from dual;

The equivalent query:

select start_time, timestamp, trunc(24 * abs(start_time - timestamp))
    ||':'|| lpad(round(60 * mod(24 * abs(start_time - timestamp), 1)), 2, '0')
    as difference
from your_table
order by id;

START_TIME          TIMESTAMP           DIFFERENCE
------------------- ------------------- ----------
2016-01-05 05:30:00 2016-01-01 10:02:29 91:28     
2016-01-30 06:10:00 2016-01-18 19:24:00 274:46    
2016-01-23 06:10:00 2016-01-08 10:46:00 355:24    
2016-01-05 05:30:00 2015-12-30 16:07:00 133:23    
2016-01-23 06:10:00 2016-01-08 12:18:05 353:52    
2016-01-01 14:10:00 2015-12-16 16:36:56 381:33    
2016-01-01 14:10:00 2015-12-16 11:41:00 386:29    
2016-01-03 05:15:00 2016-01-02 11:23:15 17:52     
2016-01-03 05:15:00 2016-01-02 07:52:00 21:23     
2016-01-16 11:15:00 2016-01-16 12:44:00 1:29      
2016-01-16 11:15:00 2016-01-16 12:50:00 1:35      

---

You can't easily compare the string value you want - and it has to be a string with a value like 91:28 - with anything else because string comparison of numbers doesn't work well. As you've see, comparing '119:54' with '11:00' is effectively comparing the third character of each string since the first two are the same, so 9 with :.

It would be simpler to leave it as a decimal fraction for comparison:

CASE
  WHEN round(24 * abs(RM_LIVE.TRANSACTIONLOG.TIMESTAMP
    - RM_LIVE.CRWGNDACTTIME.GNDACTSTART), 2) <= 11 THEN 'LESS"
ELSE 'MORE'
END AS "mORE/LESS",

For the 91:28 example, that will compare the decimal fraction version 91.46 instead; and for 119:54 will compare 119.9, which is more than 11; 102:41 will be compared as 102.68, which is also more than 11.

Or you could simplify it slightly by dividing the fixed value by 24 (hours in a day) instead of multiplying the time difference:

CASE
  WHEN abs(RM_LIVE.TRANSACTIONLOG.TIMESTAMP
    - RM_LIVE.CRWGNDACTTIME.GNDACTSTART) <= 11/24 THEN 'LESS"
ELSE 'MORE'
END AS "mORE/LESS",

dayjs diff between two date in day and hours

1. Get the difference in hours
2. Divide (and round) hours by 24 to get the days
3. Get the remainder, those are the left hours

const date1 = dayjs('2021-03-13');
const date2 = dayjs();

let hours = date2.diff(date1, 'hours');
const days = Math.floor(hours / 24);
hours = hours - (days * 24);

console.log('Days: ', days);
console.log('Hours: ', hours);

<script src="https://unpkg.com/dayjs@1.8.21/dayjs.min.js"></script>

Calculate working hours between two dates based on business hours

There are multiple phases to this calculation. It may be helpful to look at a calendar for the explanation.

1. Weeks between start and end

DATEDIFF(WEEK, cte2.START_DATE, cte2.END_DATE) As NumWeeks

This is calculating the difference between the rows of a calendar, a week. (a 7 day period generally represented as a single row on a calendar). Saturday and the following Sunday may only be one day apart, but they are in separate weeks, and therefore are in separate rows on the calendar.

---

2. Week Days between start and end.

DATEPART(WEEKDAY, cte2.END_DATE) - DATEPART(WEEKDAY, cte2.START_DATE) as NumDays

Step 1 calculated the difference in rows of the calendar, step 2 we're calculating the difference in columns. This will account for partial week differences.

Tuesday to the following Monday is six days, one day less than a week. Step one returned 1 week. Since we're shifting one column to the left, we adjusting the week by -1 days. If the end date had been Wednesday, it would be 1 week plus 1 day, but since it is Monday, it is 1 week minus 1 day.

---

3. Normalizing the Start/End time outside of working hours

CAST(CASE WHEN CAST(CTE.START_DATE AS TIME) < '08:00' THEN '08:00'
     WHEN CAST(CTE.START_DATE AS TIME) > '16:00' THEN '16:00'
     ELSE CAST(CTE.START_DATE AS TIME)
     END AS DATETIME) as StartTimeOnly,
CAST(CASE WHEN CAST(CTE.END_DATE AS TIME) < '08:00' THEN '08:00'
     WHEN CAST(CTE.END_DATE AS TIME) > '16:00' THEN '16:00'
     ELSE CAST(CTE.END_DATE AS TIME)
     END AS DATETIME) as EndTimeOnly

This calculation is only interested in the time, so we cast to Time, then back to DateTime. This sets the Date component for both to 1900-01-01.

Similar to the week & day relationship, an End Time that occurs the next day, but before the Start Time will subtract hours credited from the number of days. For example 1/2 at 12:00 to 1/3 at 10:00 would be 1 day(1/3 - 1/2), or 8 hours, - 2 hours (10-12) = 6 hours of business time.

---

4. Calculating the difference in minutes, to ensure partial hours are considered correctly, then converting to hours. This ensures times only a couple minutes apart across an hour boundary don't get counted as a full hour. Of course, the trade off is 59 minutes rounds down to 0 hours.

DATEDIFF(MINUTE, StartTimeOnly, EndTimeOnly)/60 as NumHours

If rounding at the half hour...

CAST(ROUND(DATEDIFF(MINUTE, StartTimeOnly, EndTimeOnly) / 60.0, 0) AS INT) AS RoundedHours

---

5. Wrap it all together

,
  cte2 AS
  (
    SELECT CTE.START_DATE,
           CTE.END_DATE,
           StartTimeOnly = CAST(CASE WHEN CAST(CTE.START_DATE AS TIME) < '08:00' THEN '08:00'
                                WHEN CAST(CTE.START_DATE AS TIME) > '16:00' THEN '16:00'
                                ELSE CAST(CTE.START_DATE AS TIME)
                                END AS DATETIME),
           EndTimeOnly   = CAST(CASE WHEN CAST(CTE.END_DATE AS TIME) < '08:00' THEN '08:00'
                                WHEN CAST(CTE.END_DATE AS TIME) > '16:00' THEN '16:00'
                                ELSE CAST(CTE.END_DATE AS TIME)
                                END AS DATETIME)
    FROM   CTE
  ),
  CTE3 AS
  (
    SELECT START_DATE = CAST(cte2.START_DATE AS DATETIME2(0)),
           END_DATE   = CAST(cte2.END_DATE AS DATETIME2(0)),
           NumWeeks   = DATEDIFF(WEEK, cte2.START_DATE, cte2.END_DATE),
           NumDays    = DATEPART(WEEKDAY, cte2.END_DATE) - DATEPART(WEEKDAY, cte2.START_DATE),
           NumHours = DATEDIFF(MINUTE, cte2.StartTimeOnly, cte2.EndTimeOnly)/60
    FROM   cte2
  )
SELECT CTE3.START_DATE,
       CTE3.END_DATE,
       CTE3.NumWeeks,
       CTE3.NumDays,
       CTE3.NumHours,
       TotalBusinessHours = (CTE3.NumWeeks * 5 * 8) + (CTE3.NumDays * 8) + (CTE3.NumHours )
FROM   CTE3
;

For more accurate results, you'll also want to add a table containing your holidays. You'll then subtract the number of holidays found between your start and end date from your total number of days, before converting it to hours.

A question you may still need to answer... what happens if the start and end dates occur during non-working hours? e.g. Start at 19:00 and finish at 20:00. Is that 0 business hours to resolve?

Calculate Hours Between two Date/Time Strings(YYYY-mm-dd HH:MM:SS) using Javascript

var startDate = new Date('2016-01-01 00:00:00');
var endDate = new Date('2016-01-02 23:15:00');
var time = endDate - startDate;
console.log(time/1000/60/60%24); //23.25

like this can calculate the hours span.

Showing Difference between two datetime values in hours

I think you're confused because you haven't declared a TimeSpan you've declared a TimeSpan? which is a nullable TimeSpan. Either remove the question mark if you don't need it to be nullable or use variable.Value.TotalHours.

---

Related Topics