Python: Convert Varbinary type timestamp to unix time stamp

Question

I have a mysql database that store timestamp in a varbinary column and my goal is to convert this to unix time stamp so I can do a elapse time between two time slots.

+---------+------------+-------------------+----------------+--------------------------+
| some_id | some_name  | user_registration | some_count     | some_value_yes_no        |
+---------+------------+-------------------+----------------+--------------------------+
|  some_id | some_user | 20091129060140    |           2685 |                        1 |
+---------+------------+-------------------+----------------+--------------------------+

Here the user_registration is the mysql varbinary(14), assume after connecting and extracting this data, I have the user_registration stored in a variable from python side,

...
    timestamp = row['user_registration']

how can I convert this to a unix time stamp to so I can do a time difference between a another time stamp like this. I looked at the http://docs.python.org/2/library/datetime.html but I couldn't find any example like this.

sharjeel · Accepted Answer · 2013-02-28 10:08:43Z

3

You can first convert it into a datetime object and then into a Unix time using time module.

import datetime
import time

user_reg_time = datetime.datetime.strptime("20091129060140", "%Y%M%d%H%m%S")
epochs_time = time.mktime(user_time_reg.timetuple())

BTW if you just need to compare time, you don't really need to convert to Unix time; Python has a built-in "timedelta" object. This will work too:

import datetime
import time

user_reg_time = datetime.datetime.strptime("20091129060140", "%Y%M%d%H%m%S")
if datetime.datetime.now() - user_reg_time > datetime.timedelta(weeks=4):
    print "User was registered more than 4 weeks ago"

You can get more info about timedelta at Python official docs.

answered Feb 28, 2013 at 10:08

sharjeel

6,0457 gold badges37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

add-semi-colons Over a year ago

I need to do a time difference between two time slots where both time slots are like I have mentioned, and want the difference in days

sharjeel Over a year ago

Convert the other time into datetime as well using strptime. Then subtract them and compare using datetime.timedelta(days=...)

cptPH · Accepted Answer · 2013-02-28 09:59:51Z

1

>>> import time
>>> 
>>> date_str = "20091129060140"
>>> time_tuple = time.strptime(date_str, "%Y%m%d%H%M%S")
>>> timestamp = time.mktime(time_tuple)
>>> 
>>> print timestamp
1259470900.0

answered Feb 28, 2013 at 9:59

cptPH

2,4111 gold badge30 silver badges36 bronze badges

Comments

Ivan Klass · Accepted Answer · 2013-02-28 10:18:36Z

0

If you get string it will be as following:

from datetime import datetime
datetime.strptime(timestamp, '%Y%m%d%H%M%S');

answered Feb 28, 2013 at 10:18

Ivan Klass

6,6223 gold badges32 silver badges28 bronze badges

Collectives™ on Stack Overflow

Python: Convert Varbinary type timestamp to unix time stamp

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related