I am a Python newbie and am really struggling to accumulate a number of elapsed times into a variable. The data comes from a time and attendance program. All I am looking for is the total time worked in a given period given start dates/times and end dates/time in each record. My code is as follows:
import pyodbc
import datetime
import time
fname = "\\\\frontoffice\z\data.mdb"
sname = "\\\\frontoffice\z\secured.mdw"
connect_string = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + fname +";UID=me;PWD=password;systemDB=" + sname + ";"
cnxn = pyodbc.connect(connect_string)
cursor = cnxn.cursor()
SQL = "SELECT EmpID, Weekending, Department, PayType, StartTime, EndTime FROM EmpHours WHERE EmpID=" + str(1) + " AND Weekending = #28/03/2015#;"
ttime = 0.0
for timerec in cursor.execute(SQL):
print timerec.Department, timerec.PayType,
if timerec.StartTime is not None:
print timerec.StartTime,
else:
print "",
if timerec.EndTime is not None:
print timerec.EndTime,
else:
print "",
if timerec.StartTime is not None and timerec.EndTime is not None:
print timerec.EndTime - timerec.StartTime
#ttime = ttime + timerec.EndTime - timerec.StartTime
print ttime
else:
print "n/a"
My output is:
Admin Regular 2015-03-23 17:04:33 2015-03-23 17:04:41 0:00:08
0.0
Admin Regular 2015-03-23 17:04:51 2015-03-23 17:04:57 0:00:06
0.0
Admin Regular 2015-03-23 17:05:04 n/a
Admin Regular 2015-03-23 17:05:13 2015-03-23 17:05:20 0:00:07
0.0
Admin Regular 2015-03-23 17:05:26 2015-03-23 17:05:33 0:00:07
0.0
Admin Regular 2015-03-23 17:06:09 2015-03-23 17:06:15 0:00:06
0.0
Admin Regular 2015-03-23 17:06:22 2015-03-23 17:06:28 0:00:06
0.0
Admin Regular 2015-03-23 17:06:35 2015-03-23 17:06:42 0:00:07
0.0
Admin Regular 2015-03-23 17:07:17 2015-03-23 17:07:23 0:00:06
0.0
Admin Regular 2015-03-23 17:07:29 2015-03-23 17:07:35 0:00:06
0.0
Admin Regular 2015-03-23 17:07:42 2015-03-23 17:07:48 0:00:06
0.0
Admin Regular 2015-03-23 17:08:45 2015-03-23 17:08:52 0:00:07
0.0
Admin Regular 2015-03-23 17:08:59 2015-03-23 17:09:05 0:00:06
0.0
I can't seem to change timerec.StartTime/EndTime to seconds since the epoch nor can I initialise ttime to be an empty datetime.datetime variable. Any ideas?
You already have datetime.datetime()
objects, and subtracting them gave you a datetime.timedelta()
. Just sum those objects:
ttime = datetime.timedelta()
for timerec in cursor.execute(SQL):
if timerec.StartTime and timerec.EndTime:
ttime += timerec.EndTime - timerec.StartTime
At the end of the for
loop, ttime
is a timedelta()
object representing the total time worked. You can represent that as just the number of seconds with:
ttime_seconds = ttime.total_seconds()