在 mysql 日期时间和 python 时间戳之间转换的正确方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14291636/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
what is the proper way to convert between mysql datetime and python timestamp?
提问by Max
according to http://dev.mysql.com/doc/refman/5.0/en/datetime.html. i got to find a way to convert the string value 'YYYY-MM-DD HH:MM:SS' to a timestamp int.
根据http://dev.mysql.com/doc/refman/5.0/en/datetime.html。我必须找到一种方法将字符串值 'YYYY-MM-DD HH:MM:SS' 转换为时间戳整数。
i looked up in python's doc.
我查了一下python的文档。
i tried:
我试过：
print(time.strptime('2013-01-12 15:27:43', '%Y-%m-%d %H:%M:%S'))
python give me a result like this.
python给了我这样的结果。
time.struct_time(tm_year=2013, tm_mon=1, tm_mday=12, tm_hour=15, tm_min=27, tm_sec=43, tm_wday=5, tm_yday=12, tm_isdst=-1)
time.struct_time(tm_year=2013, tm_mon=1, tm_mday=12, tm_hour=15, tm_min=27, tm_sec=43, tm_wday=5, tm_yday=12, tm_isdst=-1)
i tried this to convert timestamp to YYYY-MM-DD HH:MM:SS format
我尝试将时间戳转换为 YYYY-MM-DD HH:MM:SS 格式
print(time.strftime('%Y-%m-%d %H:%M:%S',time.time()))
python give me a type error.
python给我一个类型错误。
i only use timestamp to calculate time and date, i hope there's already a way in python, simple and efficient , and don't have to create temp data.
我只用timestamp来计算时间和日期，希望python中已经有方法了，简单高效，不用创建临时数据。
according to the answer i write two methods. hope it would be helpful
根据答案我写了两种方法。希望它会有所帮助
import time
def convertTimestampToSQLDateTime(value):
return time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(value))
def convertSQLDateTimeToTimestamp(value):
return time.mktime(time.strptime(value, '%Y-%m-%d %H:%M:%S'))
回答by RocketDonkey
Happy to update this if I'm not properly understanding, but here are a few examples which may help. Note that this uses the datetimemodule instead of time.
如果我没有正确理解，很高兴更新此内容，但这里有一些可能会有所帮助的示例。请注意，这使用datetime模块而不是time.
>>> import datetime
Here we set up an example timestamp tsand a format f:
在这里，我们设置了一个示例时间戳ts和一种格式f：
>>> ts = '2013-01-12 15:27:43'
>>> f = '%Y-%m-%d %H:%M:%S'
Similar to what you did above, we use the strptimefunction (from datetime.datetime) to convert our string into a datetimeobject based on the formatting parameter:
与您在上面所做的类似，我们使用strptime函数 (from datetime.datetime)datetime根据格式化参数将字符串转换为对象：
>>> datetime.datetime.strptime(ts, f)
datetime.datetime(2013, 1, 12, 15, 27, 43)
Now in reverse - here we use datetime.datetime.now()to get the current time as a datetimeobject:
现在反过来——这里我们使用对象datetime.datetime.now()来获取当前时间datetime：
>>> now = datetime.datetime.now()
>>> now
datetime.datetime(2013, 1, 12, 0, 46, 54, 490219)
In the datetimecase, the strftimemethod is actually called on the datetimeobject itself, with the formatting parameter as an argument:
在这种datetime情况下，该strftime方法实际上是在datetime对象本身上调用的，并将格式化参数作为参数：
>>> now.strftime(f)
'2013-01-12 00:46:54'
In your situation, the reason you were getting an error is because time.time()returns a float:
在您的情况下，您收到错误的原因是time.time()返回一个浮点数：
>>> time.time()
1357980846.290231
But time.strftimeneeds a timetuple, similar to what you had above. Without getting into the maddening spiral that is time, a function such as time.localtime()will return the aforementioned timetuple and will return as you expect:
但是time.strftime需要一个time元组，类似于你上面的。没有进入令人抓狂的时间螺旋，诸如此类的函数time.localtime()将返回上述time元组并按您的预期返回：
>>> now = time.localtime()
>>> now
time.struct_time(tm_year=2013, tm_mon=1, tm_mday=12, tm_hour=0, tm_min=55, tm_sec=55, tm_wday=5, tm_yday=12, tm_isdst=0)
>>> f = '%Y-%m-%d %H:%M:%S'
>>> time.strftime(f, now)
'2013-01-12 00:55:55'
回答by pocreagan
I'm only adding this class to potentially save the next guy a little time. If anyone finds this useful, upvote RocketDonkey's answer.
我只是添加这个类来潜在地为下一个人节省一点时间。如果有人觉得这很有用，请为 RocketDonkey 的回答点赞。
## dev on v3.7.6
from datetime import datetime
from time import mktime, time
class Time:
'''\
*Convenience class for easy format conversion*\n
Accepts time() float, datetime object, or SQL datetime str.\n
If no time arg is provided, object is initialized with time().\n
id kwarg can be used to keep track of objects.\n
Access formats as instance.t, instance.dt, or instance.sql.\
'''
f = '%Y-%m-%d %H:%M:%S'
def __init__(self, *arg, id=None) -> None:
self.id = id
if len(arg) == 0:
self.t = time()
self.dt = self._dt
self.sql = self._sql
else:
arg = arg[0]
if isinstance(arg, float) or arg == None:
if isinstance(arg, float):
self.t = arg
else:
self.t = time()
self.dt = self._dt
self.sql = self._sql
elif isinstance(arg, datetime):
self.t = arg.timestamp()
self.dt = arg
self.sql = self._sql
elif isinstance(arg, str):
self.sql = arg
if '.' not in arg:
self.dt = datetime.strptime(self.sql, Time.f)
else:
normal, fract = arg.split('.')
py_t = datetime.strptime(normal, Time.f)
self.dt = py_t.replace(
microsecond=int(fract.ljust(6, '0')[:6]))
self.t = self.dt.timestamp()
@property
def _dt(self) -> datetime:
return datetime.fromtimestamp(self.t)
@property
def _sql(self) -> str:
t = self.dt
std = t.strftime(Time.f)
fract = f'.{str(round(t.microsecond, -3))[:3]}'
return std + fract
def __str__(self) -> str:
if self.id == None:
return self.sql
else:
return f'Time obj "{self.id}": {self.sql}'
def test():
def test_one(*arg):
t = Time(*arg, id=type(*arg))
print(t)
print(t.t)
print(t.dt)
sql = '2020-01-22 15:30:33.433'
time_float = 1579927395.3708763
dt_obj = datetime.now()
for datum in [sql, time_float, dt_obj, None]:
test_one(datum)
if __name__ == '__main__':
test()
