How to calculate with time values in QGIS?

Question

I have two string fields in a vector layer, "From" and "To", with values in the format of 10:16:30 and 10:18:00 e.g.

I would like to calculate the difference in minutes as a decimal value, in this case 1.5, in the field calculator and write it to a real field for further calculations.

As I understood so far, I have to convert my strings to QT time type by using totime("From") and totime("To").

But now I already reached the end of my wisdom. No matter what I try, the field calculator doesn't accept any further combination of other functions (Expression is invalid).

Can anyone please tell me how to perform this calculation and receive a real field with a real minutes value, or can point me to some resources with working examples?

Answer 1

It's possible without using a python function, with a little bit of hacks:

minute( age( todatetime('2000-01-01 10:18:00'), todatetime(2000-01-01 10:16:30') ) )

will return "1.5".

To break it down, "age" returns the difference between two datetimes as an interval type. This needs to be wrapped in the "minute" function to extract the length of this interval in minutes. Lastly, I've had to put dummy dates into the "todatetime" functions as the "age" function only operates on datetime types, not plain times.

Answer 2

If you're using 2.8 or later, you can use the Python function builder. This code will do the calculation. I don't think you need to use Qt objects for this.

from qgis.core import *
from qgis.gui import *
from datetime import datetime
import time
@qgsfunction(args=2, group='Python')
def timedifference(values,feature,parent):
    ''' 
    call this function as timedifference("fromtime","totime")
    no need to pass in feature/parent
    '''
    fromtime = datetime.strptime(values[0], "%H:%M:%S")
    totime = datetime.strptime(values[1], "%H:%M:%S")
    a = totime - fromtime
    return a.seconds/60.0

Save this script as 'timedifference'. Go back to the expression editor.

You can then use the function in the expression editor. You should find it grouped under 'Python'

timedifference("from_field","to_field")

I've assumed here that "from_field" and "to_field" are string fields, and that "to_field" is later than "from_field".

If you're new to the Python function builder, there's a handy video here on youtube

EDIT: I don't have enough rep to comment on Nyall's answer (just joined SO) but it's a good one, much more elegant.

With a small tweak I'd suggest this:

minute( age( todatetime('2000-01-01 ' ||  "timeto" ), todatetime('2000-01-01 ' ||   "timefrom" ) ) )

Make sure you remember to add the trailing space after the date. The age() function seems to assume the later date is the first parameter.