Dr_Kerbal Dr_Kerbal - 1 year ago 70
Python Question

How to subtract timestamps from two seperate columns, then enter this data into the table

I am attempting to create a sign-in, sign-out program using python and mysql on a raspberry pi. I have so far successfully created a system for registering, signing in, and signing out, but am having trouble with the final component. I am trying to subtract the two separate time-stamps (signin, signout) using TIMESTAMPDIFF, and then entering this value into a separate column in the table. However, the statement I am using is not working.


employee_id = raw_input("Please enter your ID number")

minutes_calc = "INSERT INTO attendance (minutes) WHERE id = %s VALUES (TIMESTAMPDIFF(MINUTE, signin, signout))"


curs.execute(minutes_calc, (employee_id))

print "Error"

Table Structure so far (apologies for formatting):

name | id | signin | signout | minutes |

Dr_Kerbal 123 2016-08-21 22:57:25 2016-08-21 22:59:58 NULL

Please Note that I am in need of a statement that can subtract the timestamps regardless of their value instead of a statement that focuses on the specific timestamps in the example.

Additionally the datatype for the column minutes is decimal (10,0) as I initially intended for it to be in seconds (that was when I encountered other issues which have been solved since), its null status is set to YES, and the default value is NULL.

Thank You for your Assistance

Answer Source

You need an update query not INSERT

UPDATE attendance 
SET `minutes` = TIMESTAMPDIFF(MINUTE, signin, signout);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download