7

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?

4

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.

  • I give you the credits, cause you nicely introduced me to the new function editor plus showed a way to avoid darn dummy values for Nyall's answer. Keep posting such good answers and earn more credits :) – Bernd V. Jul 15 '15 at 7:44
6

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.

  • Thanx a lot. A pity it doesn't work directly with time values only. – Bernd V. Jul 15 '15 at 7:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.