CODEWITHSUNDEEP
pythonmysqldatetime-formattimedeltapython-datetime
asdf
asdf

Reputation: 698

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

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

Upvotes: 10

Views: 6446

Answers (3)

DigitalDesignDj
DigitalDesignDj

Reputation: 1885

I would suggest you take your own suggestion and just save the seconds.

I my experience, its almost always easier to store a UNIX timestamp (Seconds since the *nix epoch) than an actual date because it's always easy to work with.

http://en.wikipedia.org/wiki/Unix_time

Then just format the data at the last moment before output.

Seconds are a very useful way to store time, get used to it! :)

Upvotes: 1

pilcrow
pilcrow

Reputation: 58681

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.

Upvotes: 8

Nathan Villaescusa
Nathan Villaescusa

Reputation: 17659

When working with times from the database it's usually better to use the current time of the database instead of the current time of the system. If you just want to fetch the current uptime from the database, and display it, I would do so in the SQL query.

SELECT NOW()-lastrestart AS uptime FROM ...

This will return the uptime in seconds.

As for the lastrestart field I would use the MySQL TIMESTAMP type. Here are some reasons why:

Upvotes: 0

Related Questions