在Python中将mysql时间戳转换为纪元时间

Convert mysql timestamp to epoch time in python - is there an easy way to do this?

将mysql时间戳转换为python中的纪元时间 - 是否有一种简单的方法可以做到这一点?

4 个解决方案

#1

Why not let MySQL do the hard work?

为什么不让MySQL做出艰苦的努力呢?

select unix_timestamp(fieldname) from tablename;

从tablename中选择unix_timestamp(fieldname);

#2

converting mysql time to epoch:

将mysql时间转换为epoch:

>>> import time
>>> import calendar
>>> mysql_time = "2010-01-02 03:04:05"
>>> mysql_time_struct = time.strptime(mysql_time, '%Y-%m-%d %H:%M:%S')
>>> print mysql_time_struct
(2010, 1, 2, 3, 4, 5, 5, 2, -1)
>>> mysql_time_epoch = calendar.timegm(mysql_time_struct)
>>> print mysql_time_epoch
1262401445

converting epoch to something MySQL can use:

将epoch转换为MySQL可以使用的东西:

>>> import time
>>> time_epoch = time.time()
>>> print time_epoch
1268121070.7
>>> time_struct = time.gmtime(time_epoch)
>>> print time_struct
(2010, 3, 9, 7, 51, 10, 1, 68, 0)
>>> time_formatted = time.strftime('%Y-%m-%d %H:%M:%S', time_struct)
>>> print time_formatted
2010-03-09 07:51:10

#3

If you don't want to have MySQL do the work for some reason, then you can do this in Python easily enough. When you get a datetime column back from MySQLdb, you get a Python datetime.datetime object. To convert one of these, you can use time.mktime. For example:

如果你不想让MySQL因某种原因完成工作,那么你可以很容易地在Python中完成这项工作。当您从MySQLdb返回一个datetime列时,您将获得一个Python datetime.datetime对象。要转换其中一个,您可以使用time.mktime。例如:

import time
# Connecting to database skipped (also closing connection later)
c.execute("SELECT my_datetime_field FROM my_table")
d = c.fetchone()[0]
print time.mktime(d.timetuple())

#4

I use something like the following to get seconds since the epoch (UTC) from a MySQL date (local time):

我使用类似以下的东西从MySQL日期(当地时间)获得自纪元(UTC)以来的秒数:

calendar.timegm(
   time.gmtime(
      time.mktime(
         time.strptime(t, 
                       "%Y-%m-%d %H:%M:%S"))))

More info in this question: How do I convert local time to UTC in Python?

此问题中的更多信息:如何在Python中将本地时间转换为UTC?

#1