How do I subtract two times and get an hour and min result?

[Kim Sokil]

I have a start time and an end time (employee start time and end time) - and I want to calculate the difference and have a result in hh:mm.

Kim

[ParaSQL Support]

When working with dates and times many formulas will need to account for time zone offsets. The following example (for a Time field) simply subtracts one Time field from another, however it must account for the time zone offset to display a meaningful elapsed time:

@value(MyTable.End_Time) - @value(MyTable.Start_Time) + (new Date().getTimezoneOffset()*60*1000)

[Kim Sokil]

Not quite what I'm looking for.  In this case I don't care about time zones.  Example:

staff started at 8:00 am (start time) and finished at 4:30 pm (end time) = 8 hrs and 30 min.

What is the syntax to get this?  What type of field do I use for the answer?

K

[Robert Dyas]

The above example, although correct, might not be robust nor clear enough for some. Try placing this function in File > App Scripts and then calling it from the formula of a virtual time field formatted in 24 hour time:

/*
 * startTime and endTime must be values from ParaSQL Time fields.
 * Returns a value that can be displayed in a ParaSQL Time field.
 * The Time field displaying the value should be formatted in 24hr format
 * (hence why the extra hour is added to getTimeZoneOffset).
 */
function time_diff(startTime, endTime) {
 
    var d = new Date();
    var tzOffset = (d.getTimezoneOffset() * 60 * 1000) + (60*60*1000);
 
    return (startTime != null && endTime != null && startTime <= endTime) ? endTime - startTime + tzOffset : null;
}

[Robert Dyas]

Also note that @value(MyTable.MyTimeField) returns an integer representing the number of milliseconds (1,000 milliseconds in 1 second) past midnight (per the documentation).

So you can get a difference between two time fields quite easily and convert it to fractional hours (say 11:00 am to 1:30 pm is 2.5 hour diff) for use in a numeric field or calculation (not a time field) as follows:

( @value(SomeTable.EndTime) - @value(SomeTable.StartTime) ) / (1000 * 60 * 60)

[Kim Sokil]

Using your above example is there a way I can have it display as: 2 hours and 30 minutes?

What type of field would I need for the resulting field?  A Time field? 

The reason I want it in hours and minutes as we use a punch clock - and employees can punch in and out at random times  - example:

7:48 am (start) and 5:09 pm (end) = 9 hours and 21 minutes. This is what my users understand.  In your formula they would get 9.35 hours which is a bit confusing for them.

Thanks

Kim

[ParaSQL Support]

You could take the ms difference between two times or datetimes, then take that value and divided by the number of ms in an hour and then floor it to round down to get a numeric hour.

Likewise you could take a modulus to get the reminder ms and then div by ms in a min to get the number of mins.

Then you could concat those values with text and display in a char field.

[ParaSQL Support]

List of JavaScript operators here:

http://www.w3schools.com/js/js_arithmetic.asp

Also look at the various Math object functions like floor and round:

http://www.w3schools.com/js/js_math.asp