0

I have a database of General Transit Feed Specification data for a city that defines transit service after midnight as hour > 24. So, in the stop_times table, we have many times defined for example as 25:00:00, 26:00:00, etc. Since I need to perform time subtraction on part of this database, I figured I'd write up a user-defined python script to handle this and use the python create_function sqlite command to associate it with my database.

For some reason, when I run the query I have in mind on this dataset, I get 

sqlite3.OperationalError: user-defined function raised exception

Here's the time subtraction function I wrote to handle times after midnight. I'm sure it's a mess; if you have any tips for how to more efficiently handle this, I'd love to hear those as well. Thanks in advance.

def time_delta(t1, t2):
old_arrival = t1.encode('utf-8').split(':')
old_departure = t2.encode('utf-8').split(':')

new_arrival_string = "2013-03-16 %s:%s:%s" % (int(old_arrival[0])-12, old_arrival[1], old_arrival[2])
new_arrival_format = "%Y-%m-%d %H:%M:%S"
arr = datetime.datetime.strptime(new_arrival_string, new_arrival_format)

new_departure_string = "2013-03-16 %s:%s:%s" % (int(old_departure[0])-12, old_departure[1], old_departure[2])
new_departure_format = "%Y-%m-%d %H:%M:%S"
dep = datetime.datetime.strptime(new_departure_string, new_departure_format)

difference = arr-dep
seconds = difference.seconds

if difference.days < 0:
    difference = dep-arr
    seconds = (-1) * difference.seconds

return seconds
George
  • 343
  • 2
  • 15
  • Log the exception in your function. – CL. Mar 28 '13 at 08:19
  • Use [try/except](http://docs.python.org/tutorial/errors.html) inside that function. – CL. Mar 28 '13 at 14:25
  • How is it possible to do this while sqlite runs this function? When I iterate over the unsubtracted results coming out of SQL and call the function myself, no uncaught exceptions are logged through sys.excepthook. How can I log them while SQL runs the statement? – George Mar 28 '13 at 14:25

1 Answers1

0

Are you able to change the database schema? If so, one way to sidestep this problem might be to store arrival and departure times not as strings but as integer numbers of seconds since midnight (well, "noon minus 12h", as the spec defines), and change whatever tool you're using to load the database to convert from the "HH:MM:SS" format used in stop_times.txt.

Not only does this give you a nice, canonical representation of stop times that isn't bounded by any 24-hour limit, it makes it simple to compute time intervals and to construct database queries for stop times within specific time periods.