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

score 7 · Accepted Answer · answered Nov 02 '12 at 21:00

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.

score 1 · Answer 2 · answered Nov 02 '12 at 21:04

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! :)

score 0 · Answer 3 · edited May 23 '17 at 12:32

0

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:

When to use datetime or timestamp

edited May 23 '17 at 12:32

Community

1
1

answered Nov 02 '12 at 20:13

Nathan Villaescusa

17,331
4
53
56

I don't get it. I don't need to use a timestamp because: -Timestamp can not hold values before 01-01-1970 (and i need values that range from 00-00-000 to xx-xx-0004 max) - i don't need to update that field every time i access or modify the row -i'm already using a timestamp field – asdf Nov 02 '12 at 20:35
That depends on how data is being stored in the table. The `timestamp` appears to always get updated whenever the row is `inserted` or `updated`, which may or may not be desirable. edit: I posted this right after @superagio replied. I agree, you probably want to store `lastrestarted` as a datetime. – Brendan Moore Nov 02 '12 at 20:36
A `TIMESTAMP` is only updated if you tell MySQL to `ON UPDATE CURRENT_TIMESTAMP` when you setup your database table. By default it won't change unless you do so yourself. – Nathan Villaescusa Nov 02 '12 at 20:37
so there's no automatic way for converting from seconds to datetime... maybe i should do on my math and do it on my own. or store the seconds :) – asdf Nov 02 '12 at 20:45
If you are talking about a coverting a unix timestamp to a datetime you can use `datetime.fromtimestamp()` – Nathan Villaescusa Nov 02 '12 at 20:48

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

3 Answers3