What's the best way to store a python timedelta value in mysql?

Question

I need to store an uptime in a mysql environment. The uptime can vary from a few hours to more than one year. I was considering using a DATETIME type for mysql. I'm working with python, and the uptime is obtained from

def convertdate(lastrestart):
   # now in datetime
   nowdt=datetime.now()

   # last_restarted in datetime
   lastrestarted_dt=datetime.strptime(lastrestart, "%Y-%m-%d %H:%M:%S")

   # timedelta in datetime! 
   uptimeInDT=nowdt-lastrestarted_dt

   #timedelta in seconds
   secondsUptime=uptimeInDT.seconds

   # string conversion wont work so much for a datetime field.
   print str(uptimeInDT)

Is this the best way for doing this job? Sould I use other solutions? SEC_TO_TIME() in mysql has a smaller range and wont work, and there's no function from sec to datetime. Maybe I should save the seconds and get used to that. Thanks

Answer 1

MySQL does not offer a first class INTERVAL type, which type would be the SQL analog of python's timedelta.

So, to store the delta, I'd suggest simply storing the difference in seconds in an integral field large enough to hold your largest expected value.

To display the delta in a "this many days, hours, minutes, etc." format — which is what you seem to be looking for — I'd suggest doing that in client code. timedelta objects stringify nicely, or you can roll your own formatter as you like.

If you search SO for people trying to format intervals ("X seconds ago" or "X weeks ago"), you'll see relevant approaches and toolkits.